how to save currency symbol unicode in mysql table

Question

I'm using mysql table to store currency symbol unicode but when I return it to my android app in dto it showing the same unicode string(\u20B9) and not the currency sign(₹).

Earlier I was using it hard coded like dto.setCurrencyCode("\u20B9") and it was working fine but its not working if I fetch it from mysql table and set in dto.

The mysql table charset is like -

+--------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field        | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+--------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id           | bigint(20)   | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| apiUrl       | varchar(255) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| countryCode  | varchar(255) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| currencyCode | varchar(255) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| locale       | varchar(255) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| mcc          | int(11)      | NULL            | NO   | MUL | NULL    |                | select,insert,update,references |         |
| msisdnLength | int(11)      | NULL            | NO   |     | NULL    |                | select,insert,update,references |         |
+--------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+

and table entry is like -

mysql> select * from MccDetails;
+----+-------------------------------------+-------------+--------------+--------+-----+--------------+
| id | apiUrl                              | countryCode | currencyCode | locale | mcc | msisdnLength |
+----+-------------------------------------+-------------+--------------+--------+-----+--------------+
|  1 | https://localhost:8080/api/         | +91         | \u20B9       | en     | 404 |           10 |
|  2 | https://localhost:8080/api/         | +91         | \u20B9       | en     | 405 |           10 |
+----+-------------------------------------+-------------+--------------+--------+-----+--------------+

This is my connection string - jdbc:mysql://127.0.0.1:3306/dbname?autoReconnect=true&useUni‌​code=true&connection‌​Collation=utf8_gener‌​al_ci&characterSetRe‌​sults=utf8

Please let me know what should be the data type of currencyCode column and how to store the unicode value (\u20B9) in table or what is the best way to handle currency symbol in client server architecture.

Answer 1

MySQL (as per the time of this writing) does not seem to support Unicode characters based on code points. But they seem to have it on their schedule; see here.

The following things are coming to my mind to solve that problem:

First solution

Here, I am assuming that you are on a Linux UTF-8 console in the bash shell. Then you could first print out the character you are after in bash:

echo $'\u20B9';

This will print out the desired character to the console if your system is configured correctly. Then just copy the character using the mouse and in MySQL client paste it into your SQL command at the appropriate place. This should work even if you are logged in via SSH.

This solution might look non-professional, but it is pragmatic and easy if you only have a few of those symbols.

Please note that this will only work if you have configured your MySQL client to use the same encoding as the column in question. Otherwise, some garbage character(s) will be written into the row / column in question.

Second solution

While MySQL does not yet allow to enter characters based on code points, it allows entering characters based on the encoding.

From your examples, you are using UTF-8 encoding in your database / tables / columns. Thus, for this solution, we first have to get hold of the UTF-8 encoding of the character you are after.

Again, this is easy if you are on a UTF-8 console in Linux. In bash, do

echo $'\u20B9' | hexdump;

This will print out something like

0000000 82e2 0ab9                              
0000004

What we are after is the part 82e2 0ab9. Assuming we are on a little endian machine (as is the case when we are on x86 / x64), we have to reverse the bytes in each block and get e282 b90a.

But we still are not done yet: The echo command has appended a line feed at the end of its output which has become part of the hexdump; therefore, there is a LF char (0a) at the end. Of course, that line feed is not part of your character, so we have to remove it. When having done so, we finally have got the UTF-8 encoding of your character in little endian notation:

0xe282b9

Now, in MySQL client, do the following:

INSERT INTO ... (..., currencyCode, ...)
VALUES (..., 0xe282b9, ...)

Yes, you really can insert UTF-8 encoded characters into MySQL data that way!

Possibly you can do a similar thing if you are on Windows, but I did not test and don't know that. If you can't use a reasonable UTF-8 console / shell, then you can get the UTF-8 encoding of Unicode characters (code points) using online tools like this one.

Like the first solution, this one might only be appropriate if you don't need to do this too often.

Third solution

If you need to insert Unicode characters or strings routinely, you could either automate solution 1 or solution 2, or you could write a small script or program in the programming language of your choice (this could even be a bash script). I won't provide an example here because I don't know what programming language you prefer, and because your whole question makes the impression that you are looking for a solution which only uses the MySQL client.

Answer 2

It is obviously stored as a String not character.

What you have to do is to enable proper encoding on connection string like that

jdbc:mysql:///dbname?useUnicode=true&characterEncoding=utf-8"

Strings having chars like ₹ will store correctly to the database when you do that. You dont have to use \u20B9 code anymore