MySQL Basics-Encoding settings

At the beginning of the work, in the work of the database-related issues, mostly with the coding, summarize the database encoding settings.

Character set view the character set supported by the database:

SET;

You can see the character sets available in the database and the description and default proofing for each character set. Proofing: A rule for a pair of characters in a character set.

View a complete list of supported proofing.

mysql> SHOW COLLATIONS;

As you can see, there are more than one collation of the character set, for example, Latin1 has several proofing for different European languages, and many proofing occurs two times, one case-sensitive (represented by _cs, sensitive case), and one case insensitive (denoted by _ci insensitive).

Typically, system administration defines a default character set and proofreading at installation time. In addition, you can specify the default character set and proofing when you create a database. To determine the character set and proofing used, you can use the following statement:

LIKE ‘character%‘;mysql> SHOW VARIABLES LIKE ‘collation%‘;

In fact, a character set is rarely a server-wide (even database-scoped) setting, different tables, or even different columns may require different character sets, and both can be specified when the table is created.

To specify a character set and proofreading for a table, you can use a string sentence CREATE TABLE :

CREATE TABLE user(    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, username VARCHAR(10)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

In addition to the range of tables that can specify character sets and proofing, MySQL allows you to set them on no column as follows:

CREATE TABLE user (    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, username VARCHA(10), about VARCHAR(100) CHARACTER SET latin2 COLLATE latin2_general_ci) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Proofreading plays an important role in sorting the data retrieved from order by. If you need to sort a specific SELECT statement in a different order than when you created the table, you can do so in the SELECT statement itself:

SELECT * FROM customers ORDER BY lastname COLLATE latin1_general_cs;

Encoding settings MySQL connection has four encoding settings:

1. Database server-side encoding
2. Database encoding
3. Client-side encoding
4. Connection code

The above four codes have a non-conforming result that is likely to be inconsistent with expectations.

To view the coding situation:

status;

From this can be seen in four encoding database server and database encoding are LATIN1 encoding, only the client and connection is the use of UTF8, which is the default situation, after MySQL installation if not set the default is Latin1 encoding, if the Chinese language will cause a lot of trouble. Here's how to set up the code for these parts in steps.

1. Set the database server-side encoding: mainly related to configuration file modification, that is /etc/mysql/my.conf , can also be modified by the SET command. /etc/mysql/my.conffound in [mysqld] (if not manually added, in the same way, if /etc/mysql/my.conf not present, add it manually) add the following configuration information:

[mysqld]character-set-server=utf8collation-server=utf8_general_ci

Set it up here and remember to restart MySQL, if it's a Debian system like Ubuntu:

[email protected]:~$ sudo /etc/init.d/mysql restart

If the Redhat system is like CentOS:

[email protected]:~$ sudo /etc/init.d/mysqld restart

Or

[email protected]:~$ sudo systemctl restart mysqld

Re-login to MySQL using status the view, if all are utf8 that the setting is successful.

Of course, this is just setting the default encoding, which is no problem with the new library encoding, what about the already established library? This requires the encoding of the database to be set separately. ALTER DATABASE <database name> CHARACTER SET <character set>;EX:

SET utf8;mysql> ALTER DATABASE world COLLATE utf8_general_ci;

From setting up the MySQL character set this process has deepened the understanding of a truth, no matter what new things to learn, the official documents should be as the primary information, this is a really reliable shortcut. : )

Reproduced in this article!

Official Document Portal: http://dev.mysql.com/doc/refman/5.7/en/charset.html

MySQL Basics-Encoding settings