Mysql character set modification

Environment of this experiment:

Linux systems:

cat /etc/redhat-release

CentOS release 6.5 (Final)

Database environment (source code compilation, multiple instances):

select version();

+------------+

| version() |

+------------+

| 5.5.32-log |

+------------+

The simulation is the process of modifying a database of the latin1 character set to a database of the utf-8 character set

To create a database for the latin1 character set:

mysql> create database anguo default character set = latin1 default collate = latin1_swedish_ci;

Query OK, 1 row affected (0.00 sec)

Create a table for the latin1 character set:

create table student(id int(4) not null auto_increment,name varchar(20),primary key(id)) default character set = latin1 default collate = latin1_swedish_ci;

set names latin1;

insert into student(name) values ('zhang '),('he'),('hou '),('liu'),('Anguo '),('Zhang San');

Modification steps:

1)export table structure

mysqldump -uroot -pmysql -S /data/3306/mysql.sock --default-character=utf8 -d anguo>/data/backup/createanguo.sql

where-default-character=utf8 indicates what character set to connect with, -d indicates that only table structure is exported, not data

2)Manually modify the character set defined by the table structure in createoldboy.sql to a new character set

sed -i 's/latin1/utf8/g' /data/backup/createanguo.sql

Note: Make sure there are no latin1 characters in the relevant fields when replacing batches.

3)Ensure records are no longer updated, export all records

mysqldump -uroot -pmysql -S /data/3306/mysql.sock --quick --no-create-info --extended-insert --default-character-set=latin1 anguo >/data/backup/dataanguo.sql

--quick: This option is used to dump large tables preceded by mysqldump to retrieve rows from the table one at a time from the server rather than all rows and cache it in memory before output

--extended-insert: Use a multiline insert syntax that includes a list of several values, which makes the dump file smaller and can be inserted quickly when overloading files

--no-create-info: Less than create table statement for each dump table

--default-character-set=latin1: Export all data according to the original character set. In the exported file, all Chinese are visible and will not be saved as garbled characters.

4) Open dataguo.sql and change set names latin1 to set names utf8

sed -i 's/latin1/utf8/g' /data/backup/dataanguo.sql

Note: Make sure there are no latin1 characters in the relevant fields when replacing batches.

5)Create a database using the new character set

create database anguo default character set = utf8 default collate = utf8_general_ci;

6)Create the table and execute createoldboy.sql

mysql -uroot -pmysql -S /data/3306/mysql.sock anguo < /data/backup/createanguo.sql

7)Import data, perform initialization

mysql -uroot -pmysql -S /data/3306/mysql.sock anguo < /data/backup/dataanguo.sql

MySQL Database Development, Optimization and Management Maintenance