CODEWITHSUNDEEP
mysqlchar
Joseph_01
Joseph_01

Reputation: 63

In MySQL, CHAR() function works weird

When I write CHAR() function. For example:

mysql> select char(97);
+--------------------+
| char(97)           |
+--------------------+
| 0x61               |
+--------------------+

I know that this is not right.

mysql> select char(97);
+--------------------+
| char(97)           |
+--------------------+
| a                  |
+--------------------+

Maybe this is right situation, right?

Can you explain how can I fix this problem?

Upvotes: 5

Views: 2262

Answers (2)

Payel Senapati
Payel Senapati

Reputation: 1356

Although answer given by @RandomPerson is correct I don't think USING utf8mb4 should be the recommended way to approach the problem.

The CHAR() function only accepts ASCII values. Arguments larger than 255 are converted into multiple result bytes.

See https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_ascii

CHAR() arguments larger than 255 are converted into multiple result bytes. For example, CHAR(256) is equivalent to CHAR(1,0), and CHAR(256*256) is equivalent to CHAR(1,0,0): 
mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
+----------------+----------------+
| HEX(CHAR(1,0)) | HEX(CHAR(256)) |
+----------------+----------------+
| 0100           | 0100           |
+----------------+----------------+
mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));
+------------------+--------------------+
| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |
+------------------+--------------------+
| 010000           | 010000             |
+------------------+--------------------+

Thus the recommended approach to this problem should be -

 SELECT CHAR(97 USING ASCII);
+----------------------+
| CHAR(97 USING ASCII) |
+----------------------+
| a                    |
+----------------------+

Upvotes: 2

Random Person
Random Person

Reputation: 174

Prior to MySQL 8.0.19, the issue which you are facing did not exist. binary-as-hex client option is enabled by default from MySQL 8.0.19.

Run status or \s in MySQL. You will notice a line about binary data. If it is mentioned as hexadecimal, outputs of functions like CHAR() and UNHEX() will displayed as hexadecimal instead of normal text.

To avoid this you can use the USING charset clause of CHAR().

mysql> SELECT CHAR(97);
+--------------------+
| CHAR(97)           |
+--------------------+
| 0x61               |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR(97 USING utf8mb4);
+------------------------+
| CHAR(97 USING utf8mb4) |
+------------------------+
| a                      |
+------------------------+
1 row in set (0.00 sec)

Or, you can use --skip-binary-as-hex. The steps to follow in Windows are:

  1. Open Command Prompt.

  2. Change the directory. In my case the command was:

    cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

  3. Run

    mysql -u root -p --skip-binary-as-hex

  4. Enter the password.

  5. Now, the outputs of CHAR() function will be as you expect it to be.

Note: The --skip-binary-as-hex method works until you quit MySQL. You need to use --skip-binary-as-hex method every time you open MySQL such that hexadecimal values will not be shown.

You can refer these links for more information:

Upvotes: 4

Related Questions