Garbled cause
MySQL character encoding is introduced in version 4.1, supports multiple languages, and some features already exceed other database systems.
We can view the MySQL character set under MySQL command line Client by entering the following commands
Mysql> show CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default Collation | MaxLen |
+----------+-----------------------------+---------------------+--------+
| Big5 | Big5 Traditional Chinese | Big5_chinese_ci | 2 |
| Dec8 | DEC West European | Dec8_swedish_ci | 1 |
| cp850 | DOS West European | Cp850_general_ci | 1 |
| HP8 | HP West European | Hp8_english_ci | 1 |
| koi8r | Koi8-r relcom Russian | Koi8r_general_ci | 1 |
| Latin1 | cp1252 West European | Latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 European | Latin2_general_ci | 1 |
| Swe7 | 7bit Swedish | Swe7_swedish_ci | 1 |
| ASCII | US ASCII | Ascii_general_ci | 1 |
| Ujis | EUC-JP Japanese | Ujis_japanese_ci | 3 |
| Sjis | Shift-jis Japanese | Sjis_japanese_ci | 2 |
| Hebrew | ISO 8859-8 Hebrew | Hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | Tis620_thai_ci | 1 |
| Euckr | EUC-KR Korean | Euckr_korean_ci | 2 |
| koi8u | Koi8-u Ukrainian | Koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | Gb2312_chinese_ci | 2 |
| Greek | ISO 8859-7 Greek | Greek_general_ci | 1 |
| cp1250 | Windows European | Cp1250_general_ci | 1 |
| GBK | GBK Simplified Chinese | Gbk_chinese_ci | 2 |
| Latin5 | ISO 8859-9 Turkish | Latin5_turkish_ci | 1 |
| Armscii8 | ARMSCII-8 Armenian | Armscii8_general_ci | 1 |
| UTF8 | UTF-8 Unicode | Utf8_general_ci | 3 |
| UCS2 | UCS-2 Unicode | Ucs2_general_ci | 2 |
| cp866 | DOS Russian | Cp866_general_ci | 1 |
| KEYBCS2 | DOS Kamenicky Czech-slovak | Keybcs2_general_ci | 1 |
| Macce | Mac European | Macce_general_ci | 1 |
| Macroman | Mac West European | Macroman_general_ci | 1 |
| cp852 | DOS European | Cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | Latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | Cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | Cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | Cp1257_general_ci | 1 |
| binary | Binary Pseudo CharSet | binary | 1 |
| Geostd8 | GEOSTD8 Georgian | Geostd8_general_ci | 1 |
| cp932 | Sjis for Windows Japanese | Cp932_japanese_ci | 2 |
| Eucjpms | Ujis for Windows Japanese | Eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
Rows in Set (0.02 sec)
More MySQL's character set knowledge can be referenced in this forum
Http://www.phpfans.net/bbs/viewt ... &extra=page%3d1
or the MySQL official
Http://dev.mysql.com/doc/refman/5.1/zh/charset.html
MySQL 4.1 's character set support (Character set Support) has two aspects: Character set (Character set) and Sort method (collation). The support for character sets is refined to four tiers: Servers (server), databases (database), Data tables (table), and connections (connection).
The settings for viewing the system's character set and sorting can be set by using the following two commands:
Mysql> show VARIABLES like ' character_set_% ';
+--------------------------+-------------------------------------------+
| variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | Latin1 |
| character_set_connection | Latin1 |
| Character_set_database | Latin1 |
| Character_set_filesystem | binary |
| Character_set_results | Latin1 |
| Character_set_server | Latin1 |
| Character_set_system | UTF8 |
| Character_sets_dir | D:\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+-------------------------------------------+
8 rows in Set (0.06 sec)
Mysql> show VARIABLES like ' collation_% ';
+----------------------+-------------------+
| variable_name | Value |
+----------------------+-------------------+
| collation_connection | Latin1_swedish_ci |
| Collation_database | Latin1_swedish_ci |
| Collation_server | Latin1_swedish_ci |
+----------------------+-------------------+
3 Rows in Set (0.02 sec)
The values listed above are the default values for the system. Latin1 default proofing rules are latin1_swedish_ci, and the default is Latin1 's Swedish sorting method.
Why, the default would be latin1_swedish_ci, and it's easy to trace the history of MySQL.
1979, a Swedish company TCX to develop a fast multi-threaded, multi-user database system. TCX initially wanted to use mSQL and their own fast low-level routines (Indexed sequential Access Method,isam) to connect to database tables, but after some tests it came to the conclusion that mSQL was not fast and flexible for its needs. This creates a new SQL interface for a connector database that uses almost the same API interface as mSQL. This API is designed to make it easier for the Third-party code written by mSQL to be ported to MySQL.
Believe that if MySQL is developed in China, then Chinese is also the default encoding
Of course we can also need to modify the MySQL default character set
In the MySQL configuration document My.ini, find the following two sentences:
[MySQL]
Default-character-set=latin1
And
# created and no character set is defined
Default-character-set=latin1
You can modify the values that follow.
It is not recommended to change here, still leave the default value
This means that when you start MySQL, if you do not specify a default character set, this value is inherited from the configuration file;
At this point the character_set_server is set to the default character set; When a new database is created,
Unless explicitly specified, the character set of the database is set to Character_set_server by default, and when a database is selected,
Character_set_database is set to the default character set for this database; When you create a table in this database,
The table default character set is set to Character_set_database, which is the default character set for this database;
When you set a column in a table, the default character set for this column is the default character set for the table unless explicitly specified.
So the problem comes along, if a database is GBK encoded. If the character set that was not specified when accessing the database is GBK.
Then this value will inherit the system Latin1, so as to make MySQL Chinese garbled.
Garbled resolution method
To solve the garbled problem, you must first figure out what your database is encoded in. If not indicated, it will be the default latin1.
The most we use should be the 3 character set Gb2312,gbk,utf8.
So how do we specify the character set of the database? Below also GBK as an example
"Create a database in MySQL Command line client"
mysql> CREATE TABLE ' Mysqlcode ' (
-> ' id ' TINYINT (255) UNSIGNED not NULL auto_increment KEY,
-> ' content ' VARCHAR (255) Not NULL
->) TYPE = MYISAM CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 0 rows affected, 1 warning (0.03 sec)
Current 1/3 page
123 Next read the full text
