MySQL Character set encoding related

Windows 10 Home Chinese version, MySQL 5.7.20,2018-05-07

Part.1 the character set encoding of the lookup database

View MySQL Character set encoding:status Command

Log in to the MySQL server using the command line and enter the status command to view the character set encoding and other information for the server.

As you can see, the character set encoding of my server (server characterset), database (db characterset) is latin1.

Four character set encodings were found, what do they mean by each other?

-server CharacterSet

The default character set encoding for the database server, which, if no character set encoding is specified when subsequent databases are created, uses this character set encoding by default for the new database.

-db CharacterSet

When no database is currently selected (use database_name), the default character set encoding for the new database is displayed, with the server character;

The true character set encoding of the database is displayed. Displays the status information for a database with a character set encoded as UTF8.

-client characterset, Conn. characterset

The character set encoding of the client itself and the character set encoding used by the client and server to establish the connection at this time.

You can also use the show variables command to view more character set encoding information:show variables like ' char% '

Show variables can also view more information about the database and use the Help Show variables command to see its specific usage

Description, show variables command can display all system variables of the database--all or find as needed, some of the variables can be put into the database startup

Configuration file My.ini, thereby modifying the configuration information for the database. For more information and authoritative information, please refer to the website link Server System Variables.

Part.2 Modifying the database default character set encoding

Two questions:

Why would you like to modify the default character set encoding?

The default is Latin1, which does not support Chinese, and the data I want to store includes Chinese, so I want to change it.

Of course, it is not possible to change, so that you will need to specify the character set encoding each time you create a new database, as follows:

Create database if not EXISTS test2 default charset UTF8 collate utf8_general_ci;

Modify the default character set encoding why?

UTF8 ( is utf8, not utf-8, no dash )!

Two different ways:

1. Modify the command line client

Set Character_set_server=utf8;

Warning, this method is not valid after a command-line client restart.

Experiment: Create a database after modification, check the character set encoding of the new database, expected to be UTF8.

2. Modify in the database server configuration file My.ini

Add any of the following two lines in the My.ini file to set the default character set encoding to UTF8 (present under [mysqld]):

Character_set_server = UTF8
Character-set-server = UTF8

What's the difference between the two lines? The first line is underlined, the second line is connected with a dash, but it works.

Again, it is UTF8, not utf-8 (dash).

There is no first problem with this approach, but it is necessary to restart the database server for the configuration to take effect.

Part.3 Appendix

1.show variables, show status (Show database server status) display too much content-a screen display is not complete, how to do?

Output to a file!

Here's how:

Mysql-uroot-p-E "show variables" > D:\mysql.txt

Return, enter the root account password, and then enter to save the results to the D-disk Mysql.txt file (it might be better to save to a CSV file). ）。

note that the show variables command is enclosed in double quotes and the single quotation mark does not work.

2. You can use show character set; View supported character sets for MySQL database server

3. Character set encoding relationships for servers, databases, and tables

If it is not specified when creating the database, its character set is encoded with the server;

If you create a table in the database without specifying it, its character set is encoded with the same database.

However, the encoding of servers, databases, and tables can be modified from the command line (character set encoding for each field can also be specified separately).

4. View the encoding of table tables

Use Show CREATE Table ... command, as shown in.

5. Legacy issues

What does [mysqld], [client], [MySQL] mean in the My.ini configuration file?

Reference Links:

Https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

Https://dev.mysql.com/doc/refman/8.0/en/charset-configuration.html

53431852

79638776

Https://www.cnblogs.com/jiangxiaobo/p/6110647.html

Https://zhidao.baidu.com/question/539586616.html

Https://www.2cto.com/database/201112/114418.html

Https://www.cnblogs.com/candle806/archive/2013/01/14/2859721.html

MySQL Character set encoding related