Character set is a set of symbols and encoding rules, whether in the oracle database or on the mysql database, there is the choice of character set issues, and if we do not have the right to choose in the database creation stage character set, so may need to change the character set in the later, the character set of replacement cost is relatively high, also has certain risk, so, We recommend selecting the appropriate character set at the beginning of the application to avoid unnecessary adjustments later.
In actual combat
1. Install the MySQL database
2. Garbled code demonstration
mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| 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 | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
mysql> insert into test.table10 values (1,'Crane in the Cloud'); Query OK, 1 row affected (0.00 SEC) mysql> select * from test.table10; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id names | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | 1 | cloud out from | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 1 rowin set(0.00 SEC) mysql >set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test.table10;
+------+-------+
| id | names |
+------+-------+
| 1 | ??? |
+------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | latin1 The character set used by the client source data
| character_set_connection | latin1 # connection layer character set
| character_set_database | utf8 The default character set for the currently selected database
| character_set_filesystem | binary
| character_set_results | latin1 Character set | # query results
| character_set_server | utf8 # the default character set | internal operation
| character_set_system | utf8 The system metadata character set
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
Copy the codeGarbled solution
As you can see from the above, set names latin1; Changed three parameters.. MySQL character-set-client; table charset; MySQL character-set-client; MySQL character-set-client; MySQL character-set-client;
Method:
1. Run the set names XXX command in mysql.
mysql> setnames utf8; Query OK, 0 rows affected (0.01sec)Copy the code2. Specify the default character set when logging in to mysql
[root@node1 ~]Mysql -s/TMP /mysql.sock4 --defaults-character-set=utf8 # -sUse --defaults-character-set to specify the default character set.Copy the code3, modify /etc/sysconfig/i18n file when my.cnf does not specify default character set.
vim /etc/sysconfig/i18n
LANG='zh_CN.UTF-8' If my.cnf is not specified, the system character set is used by default
Copy the code4, modify the my.cnf file, the following two fields are ok in either field.
[client]
default-character-set=latin1
Copy the code [mysql]
default-character-set=latin1
Copy the codeThe first two, 1 and 2, are temporary solutions, while the last two, 3 and 4, are permanent solutions
Viewing character Set
1. Check the current character set and log in to mysql. show variables lile ‘character_set%’;
mysql> show variables like 'character_set%'; # I changed to UTf8, so the three parameters of the client are UTF8. You can set your own character set. +--------------------------+----------------------------------+ | 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 | /usr/local/mysql/share/charsets/ |Copy the codeMysql character set Run show character set; Check the character set supported by the system.
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 | Commonly used #| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |Commonly used #
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |Commonly used #
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | Commonly used #| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJISfor Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)
Copy the codeCharacter set selection
1. Select Unicode if you are working with a variety of characters and publishing to different language regions. Mysql utF-8
2, only Chinese, large amount of data, high performance requirements, choose GBK.
3. Handle mobile Internet of Things business, select UTF8MB4
It is recommended to use a small character set as long as it can fully meet the requirements of the application. A smaller character set means space savings, fewer bytes transferred over the network, and an indirect improvement in system performance due to the smaller storage space.
Server character set Settings
[mysqld]
...
character-set-server=utf8 Add this statement to set the server character set.
Copy the codeAfter the mysql server is restarted, these two parameters will change to the set values.
| character_set_server | utf8
| character_set_database | utf8
Copy the codeSwitched character set
- To convert a database of one encoding into data of another encoding.
alter database dbname character set xxx; The value is valid only for future data, not for previous data. Little use
Copy the code- Commonly used conversion database character set schemes
mysqldump -S /tmp/mysql.sock4 --default-character-set=utf8 -d test > /data/test-`date +%F`.sql #1, export table structure without exporting data
vim /data/test-`date +%F`.sql DROP TABLE IF EXISTS `table10`; / *! 40101 SET @saved_cs_client = @@character_set_client */; / *! 40101 SET character_set_client = utf8 */; CREATE TABLE `table10` ( `id` int(11) DEFAULT NULL, `names` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;#2: Change CHARSET to the character set you want to modify
mysqldump -S /tmp/mysql.sock4 --extended-insert --no-create-info test > /data/test-data-`date +%F`.sql #3. Export data content
vim /data/test-data-`date +%F`.sql
LOCK TABLES `table10` WRITE;
set names utf8; #4, This line is added, you specify the character set you want to convert
INSERT INTO `table10` VALUES (1,'Crane in the Cloud');
UNLOCK TABLES;
mysql> create database if not exists test; Create a database to store the converted data
Query OK, 1 row affected, 0 warning (0.00 sec)
mysql -S /tmp/mysql.sock4 test < /data/test-`date +%F`.sql #6 import table structure
mysql -S /tmp/mysql.sock4 test < /data/test-data-2015-09-25.sql #7 import table data
Copy the codeThe last
This is my notes on learning MySQL in 2015, and I will share them with you today