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

1) the problem of garbled Chinese data inserted by MySQL:

Simulate the garbled code phenomenon: mysql > create database kitty;Query OK, 1 row affected (0.00 sec) mysql > show create database kitty\ Gmistress * 1. Row * * Database: kittyCreate Database: CREATE DATABASE `kitty` / *! 40100 DEFAULT CHARACTER SET utf8 * / 1 row in set (0.00 sec) ERROR: No query specifiedmysql > alter database kitty character set latin1 Query OK, 1 row affected (0.01sec) mysql > flush privileges Mysql > use kitty;Database changed create teacher table (character set is latin1): CREATE TABLE `teacher` (`id` int (4) NOT NULL, `name` char (20) NOT NULL, `age`tinyint (2) NOT NULL DEFAULT '0mm, `dept` varchar (16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 inserts two pieces of data, one of which contains the Chinese characters mysql > insert into teacher values. Query OK, 2 rows affected, 1 warning (0.00 sec) Records: 2 Duplicates: 0 Warnings: 1mysql > flush privileges;Query OK, 0 rows affected (0.00 se check found garbled code: mysql > select * from teacher +-+ | id | name | age | dept | +-+ | 1 | wanlong | 31 | Server | | 2 | laomao | 31 |? | +-+ 2 rows in set (0.00 sec)

2) how to solve the garbled code

The idea of not garbled code: it is recommended to mix Chinese and English environment, choose utf-8

Client:

Set names utf8 (temporary)

Modify my.cnf (permanent)

[client]

Default-character-set=utf8

Server-library-table-program

Server: [mysqld] default-character-set=utf8 (suitable for version 5.1 and earlier) character-set-server=utf8 (suitable for 5.5) Library: view database code: mysql > show variables like'%char%' +-- +-- + | 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 | / application/mysql-5.5.32/share/charsets/ | +-- +-- + 8 rows in set (0.00 sec) Table: view the table code: mysql > show create table teacher\ G | * * 1. Row * * Table: teacherCreate Table: CREATE TABLE `teacher` (`id`int (4) NOT NULL, `name` char (20) CHARACTER SET latin1 NOT NULL, `age`tinyint (2) NOT NULL DEFAULT'0' `dept` varchar (16) CHARACTER SET latin1 DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) ERROR: No query specifiedmysql > select * from teacher +-1 | wanlong | 31 | Server | 2 | laomao | 31 |? | 3 | kobe | 35 | -+-+ 3 rows in set (0.00 sec) mysql > show create table teacher\ G * * 1. Row * * Table: teacherCreate Table: CREATE TABLE `teacher` (`id`int (4) NOT NULL, `name` char (20) CHARACTER SET latin1 NOT NULL, `age`tinyint (2) NOT NULL DEFAULT'0' `dept` varchar (16) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) ERROR: No query specified modified field character set: mysql > alter table `teacher` change `dept`dept` varchar (16) CHARACTER SET utf8 NOT NULL Mysql > alter table `teacher` change `name`name` char (20) CHARACTER SET utf8 NOT NULL;mysql > show full columns from teacher +-| Field | Type | Collation | | Null | Key | Default | Extra | Privileges | murmuri- -+-| id | int (4) | NULL | NO | | NULL | | select Insert,update,references | | name | char (20) | utf8_general_ci | NO | | NULL | | select,insert,update,references | | age | tinyint (2) | NULL | NO | | 0 | select,insert,update,references | | dept | varchar (16) | utf8_general_ci | NO | | NULL | | select,insert,update References | +-+-- +-4 rows in set (0.00 sec) mysql > select * from teacher +-1 | wanlong | 31 | Server | 2 | laomao | 31 |? | 3 | kobe | 35 | -+ 3 rows in set (0.00 sec) Test re-insert fields with Chinese characters: mysql > insert into teacher values (4 'Wanlong', 30 'Campus Network'), (5 'know what to do', 29 'East China Academy') Query OK, 2 rows affected (0.01sec) Records: 2 Duplicates: 0 Warnings: 0mysql > select * from teacher +-+ | id | name | age | dept | +-- + | 1 | wanlong | 31 | Server | | 2 | laomao | 31 |? | | 3 | kobe | 35 |? | 4 | Wanlong | | | 30 | Campus Network | | 5 | Zhixing | 29 | East China Academy | +-+ 5 rows in set (0.00 sec) |

3) the old data is still garbled!

Description:

A) for existing databases, the character set cannot be modified directly through "alter database kitty character set *" or "alter table tablename character set *". Neither of these commands updates the character set of existing records, but only for newly created tables or records.

B) for the adjustment of characters that have already been recorded, the data must be exported first, and then re-imported after modifying the character set.

Modify the database default encoding:

"alter database kitty character set *"

4) reference solution:

How to change the character set of a production MySQL database table

1. Export table structure mysqldump-uroot-predhat12345-S / data/3306/mysql.sock-- default-character-set=latin1-d kitty > kittytable.sql description:-d only import table structure 2, edit kittytable.sql, change lantin1 to utf8 can be modified in batches with sed 3, ensure that the database is no longer updated, export all data mysqldump-uroot-predhat12345-S / data/3306/mysql.sock-- quick-- no-create-info-- extended-insert-- default-character-set=latin1 kitty > kittydata.sql4, open kittydata.sql Modify set names latin1 to set names utf85 and delete original database tables and data (need to be cautious) 6. Build create database kitty default charset utf8 7. Create the table, execute kittytable.sqlmysql-uroot-predhat12345-S / data/3306/mysql.sock kitty source test.sqlQuery OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.01 sec) mysql > select * from teacher +-+ | id | name | age | dept | +-- + | 1 | wanlong | 31 | Server | | 2 | laomao | 31 |? | | 3 | kobe | 35 |? | 4 | Wanlong | | | 30 | Campus Network | | 5 | Zhixing | 29 | East China Institute | | 6 | Zhang Fei | 45 | Ministry of Commerce | +-+ 6 rows in set (0.00 sec) |

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.