Detailed graphic explanation of MySql5.7.18 character set configuration

Background:

A long time ago (2017.6.5, the article has its timeliness, especially the tools used are frequently updated, please remember this time, if it is no longer valuable, everything is subject to the official documentation of the tool), I downloaded a MySQL version to play with it, and the latest one happened to be MySQL5.7.18, and the local machine is win10, 64-bit system. The steps are roughly divided into:

1. Download: refer to the official website (https://www.mysql.com) and download the corresponding system version;

2. Initialization: Use the command line (cmd) to enter the bin folder in the decompressed directory (should I decompress it after downloading? I forgot about it after so long. Also, there is no data folder and ini files after downloading). There are two ways to initialize. One is called insecure initialization, and enter: mysqld --initialize, initialization makes the root user have no password (no password so it is not safe); the other is the so-called secure initialization, enter: mysqld --initialize-insecure, generate a root user with a password, the password can be viewed in the log file that appears after the generation, in the data file generated after the initialization

3. Start the service: Enter: net start mysql (Stop the service: Enter: net stop mysql). If it prompts that net is not an internal command, it may be that net is not installed or the environment variables are not configured. Just check it.

4. This is basically it. For other information, you can refer to the complete MySQL command.

Next, let's talk about the problem of configuring character sets.

cause:

Just yesterday, I first found it too abstract to view data on the MySQL command line, so I downloaded Navicat for MySQL (visual interface), which looked much better. As shown in the figure:

Then when inserting data into the table, an error message "Incorrect string value" will be reported. I searched on Baidu and it said that the character set should be changed to utf8mb4. This is because other encodings cannot store some special characters. You can search on Baidu for details.

go through:

To modify the character set, first enter mysql: mysql -uroot (user login, because the initialization selected the one without password), show variables like 'char%' (view 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 the default value will be restored when the service is restarted. Next, try other methods. There are many different opinions on the Internet, and they are vague and difficult to distinguish between true and false. So I went to the official website to check it out (the official website is also very watery, there are no examples to refer to, just like telling a blind person that white is white, and I am the blind person), and saw a command to view variables in the document: mysqld --verbose --help, the variables to be viewed can be configured in the option file, and each time the service is started, it will be automatically configured according to the option file, and then a sentence appears here:

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

I think it should be where the option file may exist, that is to say, the newly created option file should be named according to the name it displays and placed where it should be placed. As shown in the figure:

Create a new my.ini file and place it under D:\mysql, which happens to be one of the locations it shows, 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 shut down 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 was rejected:

2. Open it with the administrator instead, and it says the service already exists (so just delete it first):

3. Query the MySQL service: sc query mysql (You can also find it 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 you have deleted it first, otherwise the following error will occur:

6. We stopped it, this time in the control panel, because we uninstalled it before, and it disappeared after stopping (command line: net stop mysql), re-executed, and successfully started the mysql service:

7. Restart successfully, log in to see if the changes are successful:

8. Then you can stop here. I tried it a few more times. As long as I changed my.ini, shut down and restarted the mysql service, it would be changed according to my.ini every time. That is to say, if you are not satisfied in the future, you don’t have to start over again. Just change my.ini and restart the service. As shown in the figure:

I change it again:

The above is the detailed graphic explanation of MySql5.7.18 character set configuration introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!