MySQL Character set issues

Method 1: Log in to MySQL, do the set names Latin1 first, and then execute the UPDATE statement or execute the statement file MySQL>set names Latin1; Method 2: Specify set names latin1 in the SQL file, and then log in to MySQL and execute MySQL with the following command>source Test.sql; Method 3: Specify set names latin1 in the SQL file, and then import data from MySQL command MySQL-uroot-p123456 Test <Test.sqlmysql-uroot-p123456 TEST-E"Set Names Latin1;select * from Test.test;"Method 4: Implement the character set parameter by specifying the mysql command--default-character-set=latin1CatTest.sqlinsert into Test VALUES (9,'Zhang San'); MySQL-uroot-p123456--default-character-set='latin1'Test <Test.sql Method 5: Set the client and server related parameters in the configuration file [Client]default-character-set=latin1 Tip: You do not need to restart the service, log out to take effect change my.cnf parameters [Mysqld]default-character-set=Latin1 for 5.1 and previous versions character-set-server=latin1 suitable for 5.5ensure that the server and client character sets are the same Method 1: Log in to MySQL, set names Latin1 first, and then execute the UPDATE statement or execute the statement file MySQL>set names Latin1; Method 2: Specify set names latin1 in the SQL file, and then log in to MySQL and execute MySQL with the following command>source Test.sql; Method 3: Specify set names latin1 in the SQL file, and then import data from MySQL command MySQL-uroot-p123456 Test <Test.sqlmysql-uroot-p123456 TEST-E"Set Names Latin1;select * from Test.test;"Method 4: Implement the character set parameter by specifying the mysql command--default-character-set=latin1CatTest.sqlinsert into Test VALUES (9,'Zhang San'); MySQL-uroot-p123456--default-character-set='latin1'Test <Test.sql Method 5: Set the client and server related parameters in the configuration file [Client]default-character-set=latin1 Tip: You do not need to restart the service, log out to take effect change my.cnf parameters [Mysqld]default-character-set=Latin1 for 5.1 and previous versions character-set-server=latin1 suitable for 5.5ensure that the server and client character sets are the same1, Linux system serverCat/etc/sysconfig/I18nlang="Zh_cn.utf8"[[Email protected]3306]#Echo$LANGzh _CN. UTF-8Tip: Linux clients are also changed to UTF-82, MySQL database client temp: MySQL> Set names UTF8; or specify mysql-uroot-p123456--default-character-set= at startupUTF8 Permanent: Change the parameters of the MY.CNF client, you can achieve the effect of set names UTF8, and the permanent effect [Client]default-character-set=UTF8 Hint: Configuration file modification without rebooting3, MySQL server-side change My.cnf[mysqld]default-character-set=UTF8 for 5.1 and previous versions character-set-server=utf8 suitable for 5.54, MySQL database build table specified character set CREATE DATABASE test default character set UTF8 collate utf8_general_ci;5, development program character set: Simplified UTF8
#对于已有的数据库向修改字符集不能直接通过 "ALTER DATABASE character set ..." or "ALTER TABLE TableName Character set ..." Modified, both commands did not update the character set of the existing record

but for the newly created table or record to take effect, already has the record character set adjustment, must first export the data, after modifies the character set after re-import only then can complete.

The following simulates the actual process of modifying a database of the latin1 character set into a GBK character set;

1. Export Table structure
Mysqldump-uroot-p--default-character-set=latin1-d dbname >alltabl.sql            ##--default-character-set=gbk Represents the GBK character set to connect the-  D guide Table structure

2. Edit Alltable.sql to change latin1 to GBK

3. Make sure the database is no longer updated, export all data
Mysqldump-uroot-p--quick--no-create-info--extended-insert--default-character-set=latin1 dbname > Alldata.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

--no-create-info: CREATE TABLE statement is not created

--extended-insert: Use a multiline insert syntax that includes several values lists, so that the file is smaller, the IO is small, and the data is imported very quickly

--default-character-set=latin #按照原有的字符集导出数据 so that all Chinese in the exported file is visible and will not be stored as garbled

4. Open Alldata.sql to modify set names latin1 to set names GBK

5. Build the Library
Create DATABASE dbname default CharSet GBK;

6, create the table, execute Alldata.sql
Mysql-uroot-p dbname < Alltable.sql

7. Import data
Mysql-uroot-p dbname < Alldata.sql

MySQL Character set issues