search
Search
Join our weekly DS/ML newsletter layers DS/ML Guides
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
brightness_medium
share
arrow_backShare
Twitter
Facebook
0
thumb_down
0
chat_bubble_outline
0
auto_stories new
settings

MySQL | Binary and Non-binary strings

Database
chevron_right
MySQL
chevron_right
Cookbooks
chevron_right
Character sets and Collations
schedule Jul 1, 2022
Last updated
local_offer MySQL
Tags
tocTable of Contents
expand_more

Binary String

A binary string is a sequence of bytes. They have the binary character set and collation, and the comparison / sorting is done based on the numeric values of the bytes.

--Setting the character set to binary
SET NAMES 'binary';

--Checking the character set and collation
SELECT CHARSET('apple'), COLLATION('apple');
+------------------------------------+----------------------------------------+
| CHARSET('apple') | COLLATION('apple') |
+------------------------------------+----------------------------------------+
| 0x62696E617279 | 0x62696E617279 |
+------------------------------------+----------------------------------------+

When you see 0x62696E617279 this may look like gibberish at first. This is a binary string represented in hexadecimal notation (the prefix 0x indicates that the number is being written in hex):

Hexadecimal

Decimal value

ASCII character

62

98

b

69

105

i

6E

110

n

61

97

a

72

114

r

79

121

y

Hence, if we were to read the binary string 0x62696E617279 in plain English it would read 'binary'.

NOTE

In this case ASCII characters can all be represented using 1 byte, so each letter corresponds to a pair of hexadecimal digits (e.g. 'n' corresponds to hex '6E'). However, this will not be the case for multibyte characters (for example the Japanese character is represented in hexadecimal as 'E38182').

Obviously we can see here that binary strings are not very useful when we want to read the information stored as text. Binary strings are usually used to hold non-text data such as pictures and voice recordings.

Non-Binary String

A non-binary string is a sequence of characters. It is associated with a character set and collation.

The default charset in MySQL is utfmb4 and default collation is utf8mb4_0900_ai_ci:

SELECT CHARSET('apple'), COLLATION('apple');
+------------------+--------------------+
| CHARSET('apple') | COLLATION('apple') |
+------------------+--------------------+
| utf8mb4 | utf8mb4_0900_ai_ci |
+------------------+--------------------+

We can see that non-binary strings are much more suited for text information than binary strings.

mail
Join our newsletter for updates on new DS/ML comprehensive guides (spam-free)
robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
Ask a question or leave a feedback...
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!