MySQL Database garbled solution

Mysql> CREATE DATABASE wsyht_latin1 default character set latin1; #创建为拉丁字符

Mysql> Use Wsyht_latin1

mysql> CREATE TABLE t1 (ID int,age int (3), name char (10));

mysql> INSERT INTO T1 values (1,25, ' Wsyht '), (2,26, ' Peter ');

Mysql>

mysql> Select *from T1;

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

| ID | Age | name |

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

|   1 | 25 | Wsyht |

|   2 | 26 | Peter |

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

2 rows in Set (0.00 sec)

mysql> INSERT INTO T1 values (3, ' 27 ', ' Zhang San ');

mysql> Select *from T1;

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

| ID | Age | name |

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

|   1 | 25 | Wsyht |

|   2 | 26 | Peter |

|   3 |    27 |?? |

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

3 Rows in Set (0.00 sec)

mysql> set names latin1; #临时set name sets the character set of the system and library tables, temporarily modifies

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO T1 values (3, ' 27 ', ' John Doe ');

Query OK, 1 row Affected (0.00 sec)

mysql> Select *from T1;

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

| ID | Age | name |

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

|   1 | 25 | Wsyht |

|   2 | 26 | Peter |

|   3 |     27 |?? |

|   3 | 27 | John Doe |

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

4 rows in Set (0.00 sec)

Method 2

[[email protected] mnt]# cat Test.sql #先set, execute SQL statement, temporarily modify

Set names Latin1;

INSERT into T1 values (8, ' 29 ', ' Yang ');

Mysql> Source/mnt/test.sql

Query OK, 1 row Affected (0.00 sec)

mysql> Select *from T1;

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

| ID | Age | name |

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

|   1 | 25 | Wsyht |

|   2 | 26 | Peter |

|   3 |     27 |?? |

|   3 | 27 | John Doe |

|   3 | 28 | Harry |

|   8 | 29 | Yang |

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

8 rows in Set (0.00 sec)

Act 3:

[email protected] mnt]# cat Test.sql

Set names Latin1;

INSERT into T1 values (7, ' 28 ', ' Zhu Six ');

[Email protected] mnt]# mysql-uroot-pwsyht123 wsyht_latin1 < Test.sql

mysql> Select *from T1;

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

| ID | Age | name |

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

|   1 | 25 | Wsyht |

|   2 | 26 | Peter |

|   3 |     27 |?? |

|   3 | 27 | John Doe |

|   3 | 28 | Harry |

|   7 | 28 | Zhu Six |

|   8 | 29 | Yang |

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

8 rows in Set (0.00 sec)

Act 4:

[email protected] mnt]# cat Test.sql

INSERT into T1 values (4, ' 28 ', ' Harry ');

[Email protected] mnt]# mysql-uroot-pwsyht123--default-character-set=latin1 wsyht_latin1 </mnt/test.sql

mysql> Select *from T1;

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

| ID | Age | name |

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

|   1 | 25 | Wsyht |

|   2 | 26 | Peter |

|   3 |     27 |?? |

|   3 | 27 | John Doe |

|   3 | 27 | Zhang San |

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

6 rows in Set (0.00 sec)

Act 5:

Temporary:

Set Names Latin1

Permanent:

Change the parameters of the MY.CNF client module to achieve the effect of set names Latin1, and to take effect permanently

1) Ways to change the client

[Client]

Default-character-set=latin1

Hint: Do not need to restart the service, exit re-login to take effect, equivalent to set name latin1;

2) Ways to change the service side

[Mysqld]

Default-character-set=utf8 for 5.1 and previous versions

Character-set-server=utf8 Suitable for 5.5

Tip: Restart required

View the current MySQL supported character set

Mysql-uroot-pwsyht-e "show character set;"

Mysql-uroot-pwsyht123-e "show character set;" | Egrep "Gbk|utf8|latin1"

Library table

Create DATABASE wsyht default Character set uft8 collate utf8_general_ci;

The idea of not garbled: suggest Chinese and English choice Utf-8

Linux

[Email protected] mnt]# cat/etc/sysconfig/i18n

Lang= "ZH_CN. UTF-8 "

Set name Latin1

Summary: LAIN1->UFT8

1, the statement and data to build the table, SED batch modified to UTF8

mysqldump-uroot-pwsyht123 Test > T1.sql #导出test数据库所有数据包括表结构

2, modify the MySQL server and customer service side code to UTF8

Vim T1.sql

:%S/LATIN1/UTF8/GC

: Wq

3, delete the original library table and data

Drop DATABASE Test

4, import the new library and the statement to build the table

Mysql-uroot-pwsyht123 Test < T1.sql

Hope to communicate with the technology can contact me by the following ways

My OPS group 517751492

My QQ1934844044.

This article is from the "Wsyht blog" blog, make sure to keep this source http://wsyht2015.blog.51cto.com/9014030/1717695

MySQL Database garbled solution