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

The database version is 5.7.17

Phenomenon 1

When Navicat Premium imports sqlserver data to mysql, it is found that some tables import only the table structure without importing data, some tables import part of the data, and some table data are imported successfully.

Find the reason:

1. It is found that all the successfully imported data do not have any Chinese.

two。 Delete table A with only imported table structure but no imported data on mysql, use Navicat Premium to re-enter table A, and view the Navicat Premium import log [Err] [Row1] [Imp] 1366-Incorrect string value:'\ xE6\ xAD\ xA3\ xE5\ xB8\ xB8...' For column 'colunmname1' at row 1

3. Check select colunmname1 from An on sqlserver and find that it is all in Chinese.

So far, we have found out the reason: it is because of simplified Chinese that the import is not successful.

Solution method

Method 1: query the table creation statement show create table An of Table An on mysql, and find that the table creation statement is finally CHARSET=latin1. Modify the table creation statement of Table A to change CHARSET=latin1 to CHARSET=utf8, delete Table A, re-establish Table A, and then import the table to OK.

Method 2:mysql-uroot-p123456

Mysql > show variables like 'char%'; found that the character set character_set_database in the library is latin1

Modify / etc/my.cnf and add the following configuration

Add character-set-server=utf8 under [mysqld]

Add default-character-set=utf8 under [mysql]

Add default-character-set=utf8 under [client]

Service mysqld restart

Then import the table that is not completely successful on mysql, and then reimport the table (of course, in the personal experiment, the import continues to report an error, because mysql-uroot-p123456 does not select a library, and the modified character set of the newly added library becomes utf8. Of course, it is useless to choose a library. Although the database is restarted, the corresponding library in Table A, such as DATABASEA is established before the configuration is modified, its character set is still latin1. Unless you rebuild DATABASEA and change the character set to utf8, the risk is too high and everything in it will have to be done all over again)

Phenomenon 2

The mysql character set is all utf8, but when you use SQLyogEnt to query Table An on a personal windows machine, you will find Chinese garbled, which will not be garbled until SET character_set_results=gbk is added to the statement.

On the personal windows machine, use putty to log in to the mysql server and use mysql > query to find that Table An is not garbled.

Reason:

The input chcp value of PC cmd is 936 corresponding to simplified Chinese.

Mysql server echo $LANG shows zh_CN.UTF-8

Connecting mysql queries on windows through the tool SQLyogEnt is actually a query on windows, using the windows character set gbk, but the mysql character set is utf8, so it will be garbled.

Connecting to the mysql server through putty on windows is actually a query on the mysql server. The mysql server character set UTF-8,mysql character set is utf8, so there is no garbled code.

Understand

After 1.mysql installation, the database character set defaults to latin1

After 2.my.cnf modifies the character set configuration and restarts, it only takes effect for the newly added library. The old library is still latin1, and the new table of the old library is still with the old library latin1.

3. The character sets of the library and the table can be different. If you encounter a problem, whether to redirect the table or the library is evaluated.

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.