非英文网站如何使用MySQL的字符集_MySQL

非英文网站如何使用MySQL的字符集

对于非英文网站，当他们使用非英语语言从数据库中写入或读取数据时，常常必须解决字符集的问题。字符集指导数据库哪种字符编码方案用于数据的写入读取,这样可以简单地理解为字符集的一个子集整理，它告诉数据库如何存储数据。

今天我们谈论的是使用MySQL的字符集。在MySQL环境中，我们想存储中文、日文等除了英文外其它的语言，这个时候我们就要将字符集应用到数据库、表和列中。当我们连接MySQL数据库时同样也需要字符集，应该为连接设置字符集。现在，我总结了一些命令用于查看我们使用的数据的字符集以及根据需要如何改变字符集。在命令提示符窗口，首先我们需要使用 “mysql -u [name] -p” 登录mysql客户端。

接下来，我们想检查数据端和服务的一些有关于字符集的变量，例如：连接字符集。我们输入如下命令：

show variables like 'char%';

show variables like 'collation%';

执行命令后会出现如下信息提示：

+--------------------------+---------------------------------------------------------+

| 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 | C:/Program Files/MySQL/MySQL Server 5.1/share/charsets/ |

+--------------------------+---------------------------------------------------------+

对于我们的数据库引擎所使用的字符集便一目了然。我们可以令改变这些变量使用如下命令：

SET variable_name=value /* SET character_set_connection=utf8; */

进入到我们设置的字符集环境，运行：

SHOW CREATE DATABASE database_name

在输出中我们可以找到如上注释处默认的字符集。如果想改变数据库的字符集，我们执行：

ALTER DATABASE database_name CHARACTER SET charset_name COLLATE collation_name

当我们创建新的数据库时也可以设置字符集，命令：

CREATE DATABASE database_name CHARACTER SET charset_name COLLATE collation_name

对于数据库的表, 命令相似的, 执行：

SHOW CREATE TABLE table_name

在输出的最后面，可以找到“DEFAULT CHARSET or COLLATE”，如果我们想改变这些，执行：

ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name COLLATE collation_name

当我们创建新的表时也可以设置字符集，命令：

CREATE TABLE table_name (column_list) CHARACTER SET charset_name COLLATE collation_name

针对列, 需要执行：

SHOW FULL COLUMNS IN table_name

第三列是 collation. 需要如下方法改变：

ALTER TABLE table_name MODIFY col_name data_type CHARACTER SET charset_name COLLATE collation_name

通过学习以上命令, 你能够掌握MySQL字符集和collation. 如果你使用编程语言连接MySQL用于存入和读取数据，你也需要关联语言中设置字符集编码方案如PHP。

小贴士:如果你在MySQL中存储中文或是其它非英文数据，有时候你会在命令控制台中发现如上陈列的问题。你可以尝试导出外部sql文件并用文本编辑软件打开，你会惊奇发现你的中文数据再现。 这意味着你的数据存储正确，但是命令控制台中却无法正确显示。

译者注：我也遇到过“小贴士”中最后一点提到的情况。我的MySQL是5.1版，起先我在Console中使用的是UTF8字符集，表中显示的字符时中文乱码（我的表级约束是UTF8字符集），我使用 charset gbk; 命令后任然是乱码。再次使用 charset gbk; 命令，发现能正确显示中文。但是在MySQL5.0版中却无法用上述方法实现中文正确显示。

Work with MySQL character set and collation

Source : Peter

For non-English websites, they often have to deal with character set and collation if they want to store data to and read data from databases with other languages. Character set tells the database which kind of character encoding scheme to use to store or read data, collation can be simply understood as a subset of character set, it tells the database how to sort data.

We talk about working with character set and collation of MySQL today. In MySQL, if we want to store Chinese, Japanese or other languages other than English, we may need to set the relative character set for the database, tables and columns. Also, when we connect to MySQL. we may need to set the character set for the connection. Now I summarize some commands used to see what are the character set and collation of our database and how to change them as needed. On command prompt window, we need to log in to the mysql client with the mysql -u [username] -p command first.

Now we may want to check some variables about character set and collation for our database client and server, for example, connection character set. We can type following commands:

SHOW VARIABLES LIKE 'char%';

SHOW VARIABLES LIKE 'collation%';

The command will give us some information like

+--------------------------+---------------------------------------------------------+

| 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 | C:/Program Files/MySQL/MySQL Server 5.1/share/charsets/ |

+--------------------------+---------------------------------------------------------+

We can easily understand that the character set we are using for the database engine. also we can change these variables by using

SET variable_name=value /* SET character_set_connection=utf8; */

Next come to the database character set and collation, we run

SHOW CREATE DATABASE database_name

We can find our default character set in the comment of the output. If we want to change the character set and collation of the database, we run

ALTER DATABASE database_name CHARACTER SET charset_name COLLATE collation_name

We can also set the character set and collation when we create the new database

CREATE DATABASE database_name CHARACTER SET charset_name COLLATE collation_name

For database tables, the commands are similar, we run

SHOW CREATE TABLE table_name

At the end of the output, we may find the DEFAULT CHARSET or COLLATE, if we want to change them, we run

ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name COLLATE collation_name

we can also set the character set and collation when we create a table, we run

CREATE TABLE table_name (column_list) CHARACTER SET charset_name COLLATE collation_name

For columns, we need to run

SHOW FULL COLUMNS IN table_name

the third column is the collation. We can change them with

ALTER TABLE table_name MODIFY col_name data_type CHARACTER SET charset_name COLLATE collation_name

By knowing all the commands above, you may be able to handle MySQL character set and collation. If you use programming languages to connect to MySQL to store and read data, you may also need to set the character encoding scheme in relative languages such as PHP.

Finally one tip for you: If you store Chinese or other non-English data in MySQL database, sometimes you may find they are displayed as question marks in the command console. You can have a try to export the data to an external sql file and open the sql file with a text editor, you may be surprised that you can see your Chinese again. This means your data are stored properly but somehow the command console cannot display them correctly.