MySQL Character set and Collation Issue.

character-setcollationMySQL

I Have a table when i select from the table i got the invalid data as

mysql> select budgetID,StartDate,modifiedBy from Table_name order by budgetID desc limit 10;
+----------+---------------------+----------------------+
| budgetID |    StartDate        | modifiedBy           |
+----------+---------------------+----------------------+
|      364 | $091-24-68 27:49:32 | -9187343239835811836 |
|      363 | NULL                | -9187343239835811840 |
|      362 | $091-24-69 14:21:19 | -9187343239835811840 |
|      361 | $091-24-69 14:21:19 | -9187343239835811840 |
|      360 | $091-24-69 14:21:19 | -9187343239835811840 |
|      359 | $091-24-69 14:21:19 | -9187343239835811840 |
|      358 | �301-32-83 19:54:95 | -9187343239835811840 |
|      357 | �301-32-83 19:54:95 | -9187343239835811840 |
|      356 | $091-24-69 05:61:82 | -9187343239835811840 |
|      355 | �301-32-83 10:95:58 | -9187343239835811840 |
+----------+---------------------+----------------------+

i have inserted the values for budgetID=365 as

budgetID                351
StartDate               2012-02-01 00:00:00
modifiedBy              1055

but while selecting the result i am getting invalid output.

where budgetID is Primay key bigint and StartDate is datetime and modifiedBy bigint.

AND

mysql> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)


mysql> show variables like "%colla%";
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

What may the issue behind this.?

when I created a table with the same structure as this table and inserted the record then the select query is showing result OK.

Best Answer

Based on your default database encoding, character_set_database=latin1, you should force your client to use latin1. Your client is currently in utf8 and the database appears to be in latin1. This assumes that your table is also using latin1.

To make sure your data is stored correctly in MySQL, you can force the mysql client to use latin1.

1 Force the Client to use latin1

mysql -u root -p --default-character-set=latin1 nameofthedatabase

Then run your SELECT statement to verify the character formatting

select budgetID,StartDate,modifiedBy from Table_name order by budgetID desc limit 10

2 Backup the database using latin1

You can also dump the database using the same option

mysqldump -u root -p --default-character-set=latin1 nameofthedatabase > nameofthedatabase.sql

Then you could view the dump file nameofthedatabase.sql using a standard text editor like textpad or gedit to see if the characters are encoded correctly.

Collation is a set of rules that determine how rows are sorted and how they are compared. Collation does not affect the encoding which is the problem you are currently having.

If both options still give you weird symbols, the import file may not have been correctly formatted in the first place.

Related Question