MySQL Character set modification

The environment of this experiment:

Linux systems:

Cat/etc/redhat-release

CentOS Release 6.5 (Final)

Database environment:

Select version ();

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

| Version () |

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

| 5.5.32-log |

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

The process of simulating a database that modifies the UTF-8 character set of the latin1 character set database

To create a database of 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)

To create a table of the latin1 character set:

CREATE TABLE student (ID int (4) NOT null Auto_increment,name varchar), primary key (ID)) default character set = Latin1 de Fault collate = LATIN1_SWEDISH_CI;

Set names Latin1;

INSERT into student (name) VALUES (' Zhang '), (' he '), (' Hou '), (' Liu '), (' an ' "), (' Zhang San ');

To modify a step:

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 which character set to connect to,-D means export only table structure does not export data

2) manually modify the character set defined by the table structure in the Createoldboy.sql as the new character set

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

Note: When you bulk replace, make sure that there are no latin1 related characters in the relevant fields.

3) ensure that 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 a large table that has a predecessor mysqldump retrieving rows from the table one row at a time instead of retrieving all rows and caching it in memory before output

--extended-insert: Use a multiline insert syntax that includes several values lists, which makes the dump file smaller and can be quickly inserted when the file is overloaded

--no-create-info: No CREATE TABLE statement for each dump

--default-character-set=latin1: Export all data according to the original character set, so that all Chinese in the exported file is visible and will not be stored as garbled

4) open Dataoldboy.sql, change set names latin1 to set names UTF8

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

Note: When you bulk replace, make sure that there are no latin1 related characters in the relevant fields.

5) Create a database with a new character set

Create DATABASE Anguo default Character set = UTF8 default collate = Utf8_general_ci;

6) Create a 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

Note: Some of the contents refer to the development, optimization and management maintenance of MySQL database

This article is from the "HELLO Kids" blog, so be sure to keep this source http://xueanguo.blog.51cto.com/8618805/1773716

MySQL Character set modification