mysql5.7.18 character set configuration

Story background:

A long time ago (2017.6.5, the article has its timeliness, especially the tools used are updated frequently, please remember this time, if you have There is no value, everything is subject to the official documentation of the tool). I downloaded a mysql version to play with. The latest version happens to be mysql5.7.18. This machine is a win10, 64-bit system. The steps are roughly divided into:

1. Download: refer to the official website (), download responds to the system version;

2. Initialization: command line (cmd) to enter the decompression directory bin folder (download You should unzip it after downloading it, right? It took too long and I forgot about it. Also, there is no data folder and ini files when downloaded). There are two initialization methods. One is called insecure initialization, and input: mysqld --initialize, the initialization leaves the root user without a password (no password, so it is not safe); the other is the so-called secure initialization, input: mysqld --initialize-insecure, generates a password The root user, the password can be viewed in the log file that appears after generation, in the data file generated after initialization

3. Start the service: Enter: net start mysql (close the service : Enter: net stop mysql), if it prompts that net is not an internal command, then it may be that net is not installed, or the environment variables may not be configured, just check it

　4. Basically it should be like this, others can be Check out the complete list of mysql commands.

Next let’s talk about the problem of configuring the character set.

Cause:

Just yesterday, it was too abstract to view the data on the mysql command line, so I downloaded Navicat for mysql (visual interface) first, and it looked much better. As shown in the picture:

Then when inserting data into the table, an error "Incorrect string value" will be reported. Baidu said that the character set should be changed to utf8mb4. This is because other The encoding cannot store some special characters. Please refer to Baidu for details.

Pass:

To modify the character set, first enter mysql: mysql -uroot (user login, because the one without a password was selected for initialization), show variables like 'char%' (check the character encoding ), as shown in the figure:

You can enter in the command line for example: set character_set_server=utf8mb4 to change the character set, but it is only valid for that time and will be restored to the default when the service is restarted. value. Next, try other methods. There are different opinions on the Internet, and they are vague and difficult to distinguish between true and false. So I went to the official website and took a look (the official website is also very confusing, there are no examples to refer to, it's like telling a blind man that white is white, and I am that blind man), and I saw that there is a command to view variables in the document: mysqld --verbose -- help, the variables viewed can be configured in the option file. Every time the service is started, it will be automatically configured according to the options file, and then a sentence will appear here:

　　　Default options are read from the following files in the given order

I think it should be where the option file may exist, which means that the newly created option file should be named after the name it displays and placed where it should be placed. As shown in the picture:

Create a new my.ini file and place it under D:\mysql, which happens to be one of the locations it displays, as shown above. The content of my.ini is as follows:

Basically it is configured in this form. Here we only take the culprit character_set_server. Of course, the associated character set configuration must be unified, otherwise It may be garbled. You can go to Baidu to see what each character set means.

Then close the mysql service and start configuration (enter mysqld --install):

1. If a non-administrator opens cmd and enters mysqld --install, the following message will be reported, saying that the command is rejected. ：

　2. Use the administrator to open it. It says that the service already exists (then delete it first)：

　3. Query mysql service: sc query mysql (can also be found in Control Panel-Administrative Tools-Services, or search for services directly)

　4 , Delete it: (You can also uninstall it in Control Panel-Administrative Tools-Services, or search for services directly)

　5. Of course, you must stop mysql before reinstalling, even if Delete it first, otherwise it will appear:

　6. We stopped it. This time we stopped it in the control panel because we had uninstalled it before. After stopping it, it disappeared (command line: net stop mysql). We re-executed it and successfully started mysql at the same time. Service:

7. Restart successfully, log on to see if any changes have been successful:

8. Then That's it. I tried it a few times later. As long as I changed my.ini, closed and restarted the mysql service, it would change my.ini every time. That means you don't have to go through it all over again if you're not satisfied. , just change my.ini and restart the service. As shown in the picture:

I will change it again:

The above is the detailed content of mysql5.7.18 character set configuration. For more information, please follow other related articles on the PHP Chinese website!