mysql是哪五个字符集_MySQL 字符集（五）

[root@mysql-master ~]# mysql -u root -pmysql>use course;Database changedmysql>desc temp1;+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| id | int(11) | YES | | NULL | |

| name | varchar(10) | YES | | NULL | |

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

2 rows in set (0.00sec)

mysql>drop table temp1;

Query OK,0 rows affected (2.10sec)

mysql> create table temp1 (name varchar(3));

Query OK,0 rows affected (0.08sec)

mysql>show create table temp1;+-------+----------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

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

| temp1 |CREATE TABLE `temp1` (

`name` varchar(3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

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

1 row in set (0.01sec)

mysql> insert into temp1 values('aaa');

Query OK,1 row affected (0.03sec)

mysql> insert into temp1 values('aaaa');

ERROR1406 (22001): Data too long for column 'name' at row 1mysql> insert into temp1 values('中国人');

Query OK,1 row affected (0.04sec)

mysql> select * fromtemp1;+-----------+

| name |

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

| aaa |

| 中国人 |

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

2 rows in set (0.00sec)

mysql> insert into temp1 values('中国人棒');

ERROR1406 (22001): Data too long for column 'name' at row 1mysql>show create table temp1;+-------+----------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

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

| temp1 |CREATE TABLE `temp1` (

`name` varchar(3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

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

1 row in set (0.00sec)

mysql> alter table temp1 add name2 char(256);

ERROR1074 (42000): Column length too big for column 'name2' (max = 255); use BLOB or TEXT instead

mysql> alter table temp1 add name2 char(255);

Query OK,0 rows affected (0.11sec)

Records:0 Duplicates: 0 Warnings: 0mysql>show character sets;

ERROR1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sets' at line 1mysql>desc information_schema.character_sets;+----------------------+------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| CHARACTER_SET_NAME | varchar(64) | NO | | NULL | |

| DEFAULT_COLLATE_NAME | varchar(64) | NO | | NULL | |

| DESCRIPTION | varchar(2048) | NO | | NULL | |

| MAXLEN | int(10) unsigned | NO | | NULL | |

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

4 rows in set (0.01sec)

mysql> select * frominformation_schema.character_sets;+--------------------+----------------------+---------------------------------+--------+

| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |

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

| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |

| dec8 | dec8_swedish_ci | DEC West European | 1 |

| cp850 | cp850_general_ci | DOS West European | 1 |

| hp8 | hp8_english_ci | HP West European | 1 |

| koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 |

| latin1 | latin1_swedish_ci | cp1252 West European | 1 |

| latin2 | latin2_general_ci | ISO 8859-2 Central European | 1 |

| swe7 | swe7_swedish_ci | 7bit Swedish | 1 |

| ascii | ascii_general_ci | US ASCII | 1 |

| ujis | ujis_japanese_ci | EUC-JP Japanese | 3 |

| sjis | sjis_japanese_ci | Shift-JIS Japanese | 2 |

| hebrew | hebrew_general_ci | ISO 8859-8 Hebrew | 1 |

| tis620 | tis620_thai_ci | TIS620 Thai | 1 |

| euckr | euckr_korean_ci | EUC-KR Korean | 2 |

| koi8u | koi8u_general_ci | KOI8-U Ukrainian | 1 |

| gb2312 | gb2312_chinese_ci | GB2312 Simplified Chinese | 2 |

| greek | greek_general_ci | ISO 8859-7 Greek | 1 |

| cp1250 | cp1250_general_ci | Windows Central European | 1 |

| gbk | gbk_chinese_ci | GBK Simplified Chinese | 2 |

| latin5 | latin5_turkish_ci | ISO 8859-9 Turkish | 1 |

| armscii8 | armscii8_general_ci | ARMSCII-8 Armenian | 1 |

| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |

| ucs2 | ucs2_general_ci | UCS-2 Unicode | 2 |

| cp866 | cp866_general_ci | DOS Russian | 1 |

| keybcs2 | keybcs2_general_ci | DOS Kamenicky Czech-Slovak | 1 |

| macce | macce_general_ci | Mac Central European | 1 |

| macroman | macroman_general_ci | Mac West European | 1 |

| cp852 | cp852_general_ci | DOS Central European | 1 |

| latin7 | latin7_general_ci | ISO 8859-13 Baltic | 1 |

| cp1251 | cp1251_general_ci | Windows Cyrillic | 1 |

| utf16 | utf16_general_ci | UTF-16 Unicode | 4 |

| utf16le | utf16le_general_ci | UTF-16LE Unicode | 4 |

| cp1256 | cp1256_general_ci | Windows Arabic | 1 |

| cp1257 | cp1257_general_ci | Windows Baltic | 1 |

| utf32 | utf32_general_ci | UTF-32 Unicode | 4 |

| binary | binary | Binary pseudo charset | 1 |

| geostd8 | geostd8_general_ci | GEOSTD8 Georgian | 1 |

| cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 |

| eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 |

| gb18030 | gb18030_chinese_ci | China National Standard GB18030 | 4 |

| utf8mb4 | utf8mb4_0900_ai_ci | UTF-8 Unicode | 4 |

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

41 rows in set (0.01sec)

mysql>desc temp1;+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| name | varchar(3) | YES | | NULL | |

| name2 | char(255) | YES | | NULL | |

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

2 rows in set (0.00sec)

mysql>drop table temp1;

Query OK,0 rows affected (0.15sec)

mysql> create table temp1(id int,name varchar(60000),name2 varchar(6000) character setlatin1);

ERROR1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead

mysql> create table temp1(id int,name varchar(60000),name2 varchar(6000)) character setlatiin1;

ERROR1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table temp1(id int,name varchar(60000),name2 varchar(5000)) character setlatiin1;

Query OK,0 rows affected (0.12sec)

mysql>show create table temp1;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

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

| temp1 |CREATE TABLE `temp1` (

`id`int(11) DEFAULT NULL,

`name` varchar(60000) DEFAULT NULL,

`name2` varchar(5000) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

1 row in set (0.00sec)

mysql>drop table temp1;

Query OK,0 rows affected (0.10sec)

mysql> create table temp1(id int,name varchar(60000),name2 varchar(5000)) character setutf8;

ERROR1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead

mysql> select 65535/3;+------------+

| 65535/3 |

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

| 21845.0000 |

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

1 row in set (0.00sec)

mysql> create table temp1(id int,name varchar(20000),name2 varchar(1000)) character setutf88;

Query OK,0 rows affected, 1 warning (0.10sec)

mysql>drop table temp1;

Query OK,0 rows affected (0.07sec)

mysql> create table temp1(id int,name varchar(20000),name2 varchar(1000)) character setutf8mb4;

ERROR1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead

mysql> create table temp1(id int,name varchar(10000),name2 varchar(6000)) character setutf88mb4;

Query OK,0 rows affected (0.08sec)

mysql>drop table temp1;

Query OK,0 rows affected (0.04 sec)