Shulou(Shulou.com)06/01 Report--

Editor to share with you how to solve the problem of Chinese garbled code in mysql import files, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The solution to the garbled code in the mysql import file is to create the database and make the code first, and then make the code set names utf8 before importing the database file.

Continue yesterday's problem, after the database configuration, the query table found Chinese garbled, looked at the Internet several methods have not been solved.

It feels like a problem with imported sql files, so think in reverse, create a database with commands, add some data, and then export it to see what's going on.

An error was found when inserting data into the table:

ERROR 1366 (HY000): Incorrect string value:'\ xE6\ xB5\ x8B\ xE8\ xAF\ x95' for column 'bookname' at row 1

Change again decisively, through the command: alter table book change bookname bookname varchar (32) character set utf8

Don't look at it after the change, just insert the data to try:

Insert into book (id,bookname,size,price) values (1, "Test", 2pr 3)

If the insertion is successful, check whether the data is still garbled in Chinese, and find that it is not garbled:

Then proceed to the next step, export the sql file, go to the bin directory of mysql, and start exporting through the command. This process requires entering the password.

E:\ mysql-5.7.28-winx64\ bin > mysqldump-u root-p test > test.sqlEnter password: *

The exported file, the encoding format of the table utf8, compared with the previous imported file and does not see any problem.

Then, it is the process of importing the file before, and the process of creating the database apart from the problem. Looking at the mydb database structure of the previously imported mydb.sql, it is really wrong:

Mysql > use mydb;Database changedmysql > show variables like 'character_set_database' +-+-+ | Variable_name | Value | +-+-+ | character_set_database | latin1 | +-+-+ 1 row in set 1 warning (0.00 sec)

Therefore, it was revised: alert database mydb character set utf8

Then check the code of the table and find that there is a ghost:

Mysql > show create table sp_user_cart +- - - - -+ | Table | Create Table | | +-+- - - - - -- + | sp_user_cart | CREATE TABLE `sp_user_ Cart` (`cart_ id` int (11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'trickle-down marriage 'user_ id` int (11) unsigned NOT NULL COMMENT' Japanese id', 'cart_ info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT' Lu? Li Qi "? Are you handsome? Hao Bang plays the role of Huan Hai-Jun, `created_ at` timestamp NULL DEFAULT NULL, `delete_ at` timestamp NULL DEFAULT NULL, `delete_ time` timestamp NULL DEFAULT NULL, PRIMARY KEY (`cart_ id`) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

How did the utf8mb4_unicode_ci thing come out?

Then changed the code, query, or wrong, remember to create the database before, may be forgotten to set the code, imported things are already garbled, or my.ini configuration is wrong, try using the correct process from scratch.

First check the database encoding settings: show variables like 'character%'

Mysql > show variables like 'character%' +-+ | Variable_name | Value | + -- +-+ | character_set_client | gbk | | character_set_connection | gbk | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | gbk | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | D:\ mysql-5. 7.29-winx64\ share\ charsets\ | +-+-- +

Start to change, through a series of set

Set character_set_client = utf8

Get the result

+-+ | Variable_name | Value | + -- +-+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | utf8 | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | D:\ mysql | -5.7.29-winx64\ share\ charsets\ | +-+-- +

Create the database and code it: CREATE DATABASE test2 CHARACTER SET utf8 COLLATE utf8_general_ci

Use: use test2

Develop the coding set names utf8 before importing the database file

After a long wait, the query is not garbled.

Mysql > select * from sp_role +- -- +- -+-+ | role_id | role_name | ps_ids | ps_ca | role_desc | +-+- -+- -+- -+-+ | 30 | Supervisor | 101 Magi 0 104116115142143144121122149102107109103111291304135135138139140141112147125110131132133136137145146146148 | 31 | Test role | 10110105116117115142144121122149103111129134138112147 | Goods-showlist,Goods-tianjia,Category-showlist,Order-showlist,Order-dayin Order-tianjia | Test role description | | 34 | Test role 2 | 0105116142143122 | NULL | Test description 12 | 39 | send to | 101Power0 104105116 | NULL | Astefen | | 40 | test | 102Pol 0107109154155145146148 | NULL

The actual combat project can continue, thus giving birth to the joy of the law.

I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.