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

1. The method of inserting non-garbled data into mysql database 1.1 to create test data

(1) create a linzhongniao test database and view the table creation statement

Mysql > create database linzhongniao;Query OK, 1 row affected (0.00 sec) mysql > show create database linzhongniao\ G * * 1. Row * * Database: linzhongniaoCreate Database: CREATE DATABASE `linzhongniao` / *! 40100 DEFAULT CHARACTER SET latin1 * / 1 row in set (0.00 sec)

Tip: if you do not specify a character set or if you specify a latin character set when you install MySQL, the mysql default character set is latin1.

(2) create a student table under the linzhongniao library, and view the table structure and table creation statements

Mysql > use linzhongniaoDatabase changedmysql > show tables;+-+ | Tables_in_linzhongniao | +-+ | student | +-+ 1 row in set (0.00 sec) mysql > create table student (id int (4) NOT NULL AUTO_INCREMENT, name char (20) NOT NULL, PRIMARY KEY (id)); mysql > desc student +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (4) | NO | PRI | NULL | auto_increment | | name | char (20) | NO | | NULL | | +-+-+ 2 rows in set (0.00 sec) mysql > show create table student\ G* * * 1. Row * * Table: studentCreate Table: CREATE TABLE `student` (`id` int (4) NOT NULL AUTO_INCREMENT `name` char (20) NOT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin11 row in set (0.00 sec)

Tip: if you do not specify a character set by default, you will inherit the character set of the library, that is, latin1.

(3) insert data into student table in batch

Mysql > insert into student values (1 Duplicates: 0mysql > select * from student), (3 Duplicates: 0 Warnings: 0mysql > select * from student); Query OK, 4 rows affected (0 01 sec) +-+-- +-+ | id | name | +-+-+ | 1 | zhangsan | | 2 | lisi | | 3 | xiaozhang | | 4 | xiaohong | +-+-+ 4 rows in set (0.00 sec)

Tip: numbers and English data are normal, there will be no garbled problem.

(4) insert two pieces of Chinese data

Mysql > insert into student values; Query OK, 1 row affected, 1 warning (0.00 sec) mysql > insert into student values; Query OK, 1 row affected (0.00 sec) mysql > select * from student +-+-+ | id | name | +-+-+ | 1 | zhangsan | 2 | lisi | 3 | xiaozhang | 4 | xiaohong | | 5 |? | | 6 | +-+-+ 8 rows in set (0.00 sec)

Problem: garbled Chinese content

Why is there a garbled problem when inserting Chinese data?

From the above example, we can see that the difference between the client character set and the library, table character set leads to garbled problems, so when we insert data, we should first check whether the system character set is the same as the client character set and whether the database table character set is the same. modify the character set consistently and then insert the data. If the inserted data has garbled codes, you can export the backup and add the command to modify the character set and then import it again.

Method one executes the set names command

Command syntax: set names followed by the specified character set

(1) check the table creation statement and note that the default character set is latin1.

Mysql > show create table student\ gateway * 1. Row * * Table: studentCreate Table: CREATE TABLE `student` (`id` int (4) NOT NULL AUTO_INCREMENT, `name` char (20) NOT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin11 row in set (0.00 sec)

(2) set the character set of the MySQL client to be consistent with the character set latin1 of the table.

Set the character set of the inserted data to latin

Mysql > set names latin1;Query OK, 0 rows affected (0.00 sec)

(3) insert another piece of Chinese data

Mysql > insert into student values; Query OK, 1 row affected (0.00 sec) mysql > select * from student where id='7';+----+-+ | id | name | +-- +-+ | 7 | Forest Bird | +-+ 1 row in set (0.00 sec)

Tip: there is no garbled code, but the previous data can not be solved.

The above is the MySQL command line to insert the data is not garbled, so if the updated data needs to be updated in the sql file to update the data, so to ensure that the execution of the sql file is not garbled how to do?

1.3Method 2 execute sql file

(1) put multiple sql statements to be updated in a text file such as test.sql

You need to use the system command, and execute the system command to reference and view the files of the system without exiting the database. Of course, you can also exit the database, which will be troublesome.

Mysql > system cat test.sqlset names latin1;insert into student values; mysql > system ls;beifen.sh test.sql

Tip: set names latin1 must be added to ensure that the inserted data is not garbled.

(2) insert data in the MySQL command line by calling test.sql file through source

Use the source command to execute the sql file to realize the operation of the database, you can recover the data of the database, of course, you can also exit the database and use the input redirection to execute the sql file to recover the data of the database.

Mysql > source test.sqlQuery OK, 0 rows affected (0.01sec) Query OK, 1 row affected (0.00 sec) mysql > select * from student where id='8';+----+-+ | id | name | +-- +-+ | 8 | No recognition | +-+ 1 row in set (0.00 sec)

Summary: when executing the DQL,DML statement, set names should keep the character set of the library and table consistent, and adjust the character set of the client.

1.3 method 3 add character set parameters to solve the problem of garbled imported data

(1) put multiple SQL statements to be updated into the text, this time without set names latin1

# set names latin1;insert into student values (9 'Xiao Hong')

(2) specify latin1 character set and import test.sql through MySQL command plus character set parameter

[root@localhost] # mysql-uroot-p123456-- default-character-set=latin1 linzhongniao

< test.sql (3)通过-e参数在mysql库外查看结果 [root@localhost ~]# mysql -uroot -p123456 -e "select * from linzhongniao.student where id='9'"+----+--------+| id | name |+----+--------+| 9 | 小红 |+----+--------+1.4 MySQL插入中文不乱码5中方法小结 方法一：执行set names命令再插入数据 mysql>

Set names latin1;Query OK, 0 rows affected (0.00 sec) mysql > insert into student values; Query OK, 1 row affected (0.00 sec)

Tip: make sure the test.sql file is in the correct format

Method 2: specify set names latin1; in the sql file and then log in to mysql and execute it with the following command.

Mysql > system cat test.sqlset names latin1;insert into student values; mysql > source test.sqlQuery OK, 0 rows affected (0.00 sec)

Method 3: specify set names latin1 in the sql file and import through mysql

[root@localhost] # mysql-uroot-p123456 linzhongniao

< test.sql[root@localhost ~]# mysql -uroot -p123456 -e "set names latin1;select * from linzhongniao.student" 提示：这里的linzhongniao是库名不是表名。 方法四：通过指定mysql命令的字符集参数来实现 #set names latin1;insert into student values(9,'李四');[root@localhost ~]# mysql -uroot -p123456 --default-character-set=latin1 linzhongniao < test.sql 方法五：在配置文件里设置客户端及服务端相关参数 (1)更改my.cnf客户端client模块的参数，可以实现set names latin1效果，并永久生效 [client]default-character-set=latin1 提示：不需要重启服务，退出重新登陆生效。 (2)在服务端mysqld模块里面再指定latin1字符集 [mysqld]default-character-set=latin1适合5.1 及以前呢版本default-character-server=latin1 适合5.52.mysql数据库字符集知识2.1 MySQL数据库字符集介绍 简单的说是一套文字符号及其编码、比较规则的集合。MySQL数据库字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。其中，字符集是用来定义MySQL数据字符串的存储方式，而校对规则则是定义比较字符串的方式。前面建库的语句中CHARACTER SET latin1即为数据库字符集而COLLATE latin1_swedish_ci 为校对字符集，有关字符集详细内容参考mysql手册，第10张字符集章节。 2.2 MySQL数据库常见字符集介绍 使用MySQL时常用的字符集有下表四种 2.3 MySQL如何选择合适的字符集 (1)如果处理各种各样的文字，发布到不同国家和地区，应选Unicode字符集。对mysql来说就是UTF-8（每个汉字三个字节），如果应用需处理英文，有少量汉字使用UTF-8字符集更好。 (2)如果只需支持中文，并且数据量很大，性能要求也很高，可选GBK（定长，每个汉字占双字节，英文也占双字节），处理大量运算，比较顺序等定长字符集更快，性能高。 (3)处理移动互联网业务，可能需要使用utf8mb4字符集。 2.4 查看当前MySQL系统支持的字符集 最常用的有四种： [root@localhost ~]# mysql -uroot -p123456 -e "SHOW CHARACTER SET;"|egrep "gbk|utf8|latin1"|awk ' {print $0}'latin1 cp1252 West European latin1_swedish_ci 1gbk GBK Simplified Chinese gbk_chinese_ci 2utf8 UTF-8 Unicode utf8_general_ci 3utf8mb4 UTF-8 Unicode utf8mb4_general_ci 4 查看mysql当前的字符集设置情况 mysql>

Show variables like 'character_set%' +-- +-- + | Variable_name | Value | + -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | +-+- -- +

Tip: by default, the character set of character_set_client,character_set_connection,character_set_results is consistent with that of the system and is modified at the same time. That is:

What is the default character set for [root@localhost ~] # cat / etc/sysconfig/i18n LANG= "zh_CN.UTF-8" [root@localhost ~] # echo $LANGzh_CN.UTF-83.mysql database?

(1) first take a look at the character set set by mysql by default

Mysql > show variables like 'character_set%' +-- +-- + | Variable_name | Value | + -+ | character_set_client | gb2312 | | character_set_connection | gb2312 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | gb2312 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | + -- +

(2) the meanings of different character set parameters are as follows

| | character_set_client | latin1 client character set | character_set_connection | latin1 connection character set | character_set_database | latin1 database character set, specified by configuration file or database creation table | character_set_results | latin1 returned result character set | character_set_server | latin1 server character set, configuration file or database creation table specification |

After changing the linux system character set variable, view the character set changes in MySQL

[root@localhost ~] # echo $LANGzh_CN.UTF-8 [root@localhost ~] # mysql-uroot-p123456-e "show variables like 'character_set%' "+-- +-- + | Variable_name | Value | +-+- -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | +-+- -- +

We found that the character set and system consistency of character_set_connection,character_set_client,character_set_server have also been changed to utf8.

4. What exactly did the executive set names latin1 do?

Whether the character set of the linux system is gb2312 or utf8, the inserted data is garbled by default.

(1) the data viewed at this time is garbled.

Mysql > use linzhongniaoDatabase changedmysql > select * from student- > +-+-+ | id | name | +-+-+ | 1 | zhangsan | | 2 | lisi | | 3 | wanger | 4 | xiaozhang | | 5 | xiaowang | | 6 |? | 7 | °? -+ 9 rows in set (0.10 sec)

(2) after performing the character set operation corresponding to set, the problem of garbled code is solved.

Mysql > show create database linzhongniao\ sec * 1. Row * * Database: linzhongniaoCreate Database: CREATE DATABASE `linzhongniao` / *! 40100 DEFAULT CHARACTER SET latin1 * / 1 row in set (0.00 sec) mysql > show create table student\ G* * 1. Row * * Table: studentCreate Table: CREATE TABLE `student` (`id` int (4) NOT NULL AUTO_INCREMENT `name` char (20) NOT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin11 row in set (0.00 sec)

We see that the character set of both the library and the table is latin1, so execute set names latin1 to ensure that the character set will not be garbled.

Mysql > set names latin1;Query OK, 0 rows affected (0.00 sec) mysql > select * from student;+----+-+ | id | name | +-- +-+ | 1 | zhangsan | | 2 | lisi | | 3 | wanger | | 4 | xiaozhang | | 5 | xiaowang | 6 |? | 7 | Xiao Hong | | 8 | No | | 9 | Li Si | +-+-+ |

(3) the result of performing the set character set operation changes the parameters of the following three character sets character_set_client,character_set_connection,character_set_results.

Mysql > show variables like 'character_set%' +-- +-- + | Variable_name | Value | + -+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | + -- + 5.mysql command argument-what is default-character-set=latin1 doing?

(1) check the character set of mysql first

[root@localhost] # mysql-uroot-p123456-e "show variables like 'character_set%' "+-- +-- + | Variable_name | Value | +-+- -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | +-+- -- +

(2) Log in to mysql with-default-character-set=latin1 parameter

[root@localhost] # mysql-uroot-p123456-- default-character-set=latin1Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 7Server version: 5.5.32 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >

(3) now look at the character set of mysql

Mysql > show variables like 'character_set%' +-- +-- + | Variable_name | Value | + -+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | + -- +

(4) logging in with parameters is also temporarily modified and changed back without parameters.

[root@localhost] # mysql-uroot-p123456-- default-character-set=latin1-e "show variables like 'character_set%' "+-- +-- + | Variable_name | Value | +-+- -+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | + -- + [root@localhost] # mysql-uroot-p123456-e "show variables like 'character_set%' "+-- +-- + | Variable_name | Value | +-+- -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | +-+- -+ 6. Ensure that data is not garbled in MySQL database solution 6.1 uniform client character set

(1) the following character sets (client and server) of the MySQL database are unified into one character set to ensure that the inserted Chinese database can be output normally. Of course, the character set of the linux system should be consistent with the database character set as much as possible.

(2) the meaning of mysql database character set:

Variable_name | Value +-+-- + ① character_set_client | latin1 client character set ② character_set_connection | latin1 connection character set ③ character_set_database | latin1 database character set ④ Character_set_results | latin1 returns the resulting character set ⑤ character_set_server | latin1 server character set Configuration file formulation or database creation table assignment

By default, the three ①②④ parameters are set by the linux system character set. Manual login to the database to perform set names latin1 and mysql specified character set login operations all change the character set of the client, connection and results3 parameters of the mysql client to latin1, thus solving the problem of inserting garbled codes. This operation can modify the character set of the mysql client in the my.cnf configuration file. The configuration method is as follows:

[client] Default-character-set=latin1 hint: no need to restart [root@localhost ~] # sed-n "18Power22p" / etc/my.cnf [client] # password = your_passwordport = 3306socket = / usr/local/mysql/tmp/mysql.sockdefault-character-set = latin1 [root@localhost ~] # mysql-uroot-p123456-e "show variables like 'character_set%'" "+-- +-- + | Variable_name | Value | +-+- -+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | + -- +

(3) after modifying the client character set, the set query table data will not be garbled.

[root@localhost ~] # mysql-uroot-p123456-e "select * from linzhongniao.student;" +-+-+ | id | name | +-+-+ | 1 | zhangsan | | 2 | lisi | | 3 | wanger | 4 | xiaozhang | 5 | xiaowang | | 6 |? | | 7 | Xiao Hong | 8 | No | 9 | Li Si | +-- +-- + 6.2 Unified MySQL server character set

(1) modify the my.cnf parameters as required below

[mysqld] default-character-set = latin1 for version 5.1 and earlier character-set-server = utf8 for version 5.5

(2) View the current character set before modification

[root@localhost] # mysql-uroot-p123456-e "show variables like 'character_set%' "+-- +-- + | Variable_name | Value | +-+- -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | +-+- -- +

(3) View the modified parameters

[root@localhost ~] # sed-n "26je 27p" / etc/my.cnf [mysqld] character-set-server = utf8

(4) restart mysql service (restart is not allowed in production environment)

[root@localhost ~] # / etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS!

(5) View the changed character set

[root@localhost] # mysql-uroot-p123456-e "show variables like 'character_set%' "+-- +-- + | Variable_name | Value | +-+- -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | +-+- -- +

Tip: the above parameters set under [mysqld] will change the character set settings of the following two parameters.

| | Variable_name | Value | | character_set_database | utf8 | | character_set_server | utf8 |

At this time, if we modify the system character set, the mysql database character set will not be garbled.

[root@localhost ~] # cat / etc/sysconfig/i18n LANG= "zh_CN.GB2312" # LANG= "zh_CN.UTF-8" [root@localhost ~] # source / etc/sysconfig/i18n [root@localhost ~] # mysql-uroot-p123456-e "show variables like 'character_set%' "+-- +-- + | Variable_name | Value | +-+- -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | +-+- -- + 6.3 Unified character set method Summary

The method to ensure that the database data is not garbled: it is recommended to choose utf8, linux system, client, server, library, table and program character set in Chinese and English environment.

(1) Linux system character set Unified utf8

[root@localhost ~] # cat / etc/sysconfig/i18n LANG= "zh_CN.UTF-8"

Prompt the linux client to also change the character set for example: xshell

For example: SecureCRT

(2) Mysql database client

Temporary:

Set names latin1

Permanent:

Change the parameters of the my.cnf client module to achieve the set names latin1 effect and take effect permanently.

[client]

Default-character-set=latin1

(3) Service side

Change my.cnf parameters

[mysqld] Default-character-set = latin1 is suitable for 5.1and previous versions character-set-server = latin1 is suitable for 5.5,

(4) Library table, the program specifies the character set to build the database

Add proofreading rules after create database linzhongniao_utf8 DEFAULT CHARACTER SET UTF8 COLLATE

We can show to see the supported proofreading rules.

[root@localhost ~] # mysql-uroot-p123456-e "SHOW CHARACTER SET;" | egrep "gbk | utf8 | latin1" | awk'{print $0} 'latin1 cp1252 West European latin1_swedish_ci 1gbk GBK Simplified Chinese gbk_chinese_ci 2utf8 UTF-8 Unicode utf8_general_ci 3utf8mb4 UTF-8 Unicode utf8mb4_general_ci 47. How to change the character set of database tables in production MySQL database

For existing databases, the character set cannot be modified directly through "alter database character set" or "alter table tablename character set". Neither of these commands can update the character set of existing data. It only takes effect on newly created tables or data.

The adjustment of the character set that has been recorded must export the data and re-import it after modifying the character set.

Modify the database default encoding command:

Alter database [your db name] charset [your character setting]

The following simulates the process of modifying the database of the latin1 character set to the GBK character set.

(1) Export table structure

Export the table structure using the-d parameter of mysqldump

Mysqldump-uroot-p123456-- default-character-set=latin1-d dbname > alltable.sql-- default-character-set=gbk indicates concatenation in the GBK character set-d boot-only table structure.

(2) then edit alltable.sql to replace latin1 with sed with GBK

(3) make sure that the data is not updated and exported.

Mysqldmup-uroot-p123456-quick-no-create-info-extended-insert-default-character-set=latin1 dbname > alltables.sql

Parameter description:

-- quick: used to dump large tables, forcing mysqldump to retrieve data from the server one row at a time instead of retrieving all rows and outputting the CACHE into memory. -- no-create-info: no CREATE TABLE statements are created. -- extended-insert: use a multi-line INSERT syntax that includes several VALUES lists, so that the file is smaller and saves IO importing data very quickly. -- default-character-set=latin1 exports data according to the original character set, so that all Chinese characters in the exported file are visible and will not be saved as garbled.

(4) Open alltable.sql to modify set names latin1 to set names gbk (or modify my.cnf configuration file)

(5) Building a database

Create database dbname default charset gbk

(6) create table execution, alltable.sql

Mysql-uroot-p123456 dbname

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.