How to use MySQL character set for non-English websites _ MySQL

How to use MySQL character set bitsCN.com for non-English websites

How to use MySQL character sets for non-English websites

For non-English websites, when they use non-English languages to write or read data from the database, it is often necessary to solve the character set problem. Character Set guides which character encoding scheme of the database is used for data writing and reading. this can be simply understood as a subset of the character set, which tells the database How to store data.

Today we are talking about using the MySQL character set. In the MySQL environment, we want to store Chinese, Japanese, and other languages except English. at this time, we need to apply the character set to databases, tables, and columns. Character set is also required when we connect to the MySQL database. we should set the character set for the connection. Now, I have summarized some commands for viewing the character set of the data we are using and how to change the character set as needed. In the command prompt window, you must first use "mysql-u [name]-p" to log on to the mysql client.

Next, we want to check some variables related to character sets on the data end and service, such as the connected character set. Run the following command:

Show variables like 'char % ';

Show variables like 'collation % ';

After the command is executed, the following message is displayed:

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

| 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 Server 5.1/share/charsets/|

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

The character set used by our database engine is clear at a glance. We can use the following command to change these variables:

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

Go to the character set environment we set and run:

Show create database database_name

In the output, we can find the default character set in the above comments. To change the character set of the database, run the following command:

Alter database database_name character set charset_name COLLATE collation_name

When creating a new database, you can also set the character set. command:

Create database database_name character set charset_name COLLATE collation_name

For database tables, commands are similar. execute:

Show create table table_name

At the end of the output, you can find "default charset or COLLATE". if we want to change this, execute:

Alter table table_name convert to character set charset_name COLLATE collation_name

When creating a new table, you can also set the character set. command:

Create table table_name (column_list) character set charset_name COLLATE collation_name

For columns, run the following command:

Show full columns in table_name

The third column is collation. you need to change it as follows:

Alter table table_name MODIFY col_name data_type character set charset_name COLLATE collation_name

After learning the preceding commands, you can master the MySQL character set and collation. if you use programming languages to connect MySQL to store and read data, you also need to set the character set encoding scheme in the associated language, such as PHP.

Tips: If you store Chinese or other non-English data in MySQL, you may find the problem shown above in the command console. You can try to export an external SQL file and open it with text editing software. you will be surprised to find that your Chinese data is reproduced. This means that your data is properly stored, but cannot be correctly displayed in the command console.

Note: I have also encountered the last point mentioned in "tips. My MySQL version is 5.1. At first I used the UTF8 character set in the Console. Chinese characters are garbled during the characters displayed in the table (my table-level constraints are the UTF8 character set ), I use charset gbk; the command is garbled. Use the charset gbk command again to display Chinese characters correctly. However, the above method cannot be used in MySQL to display Chinese correctly.

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 ages. 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 classes ages 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 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

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 versions to connect to MySQL to store and read data, you may also need to set the character encoding scheme in relative versions ages 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.

BitsCN.com