create table chars(c CHAR(6), vc VARCHAR(6));

# char創(chuàng)建表
mysql> create table chars_c_256(c CHAR(256));
ERROR 1074 (42000): Column length too big for column 'c' (max = 255); use BLOB or TEXT instead
mysql> create table chars_c_0(c CHAR(0));
Query OK, 0 rows affected (0.00 sec)
mysql> create table chars_c_1(c CHAR(1));
Query OK, 0 rows affected (0.00 sec)
mysql> create table chars_c_255(c CHAR(255));
Query OK, 0 rows affected (0.01 sec)

# 查看mysql server默認(rèn)字符集
show variables like '%character_set_server%';
mysql> show variables like '%character_set_server%';
+----------------------+---------+
| Variable_name ? ? ? ?| Value ? |
+----------------------+---------+|?
character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.00 sec)
# 由于server端使用的字符集是utf8mb4，utf8mb4最大占用4bytes，單行最大字節(jié)數(shù)是65535bytes，因此整行可以完整存儲(chǔ)的最大字符數(shù)16383
# varchar創(chuàng)建表
mysql> create table vchars_c_16384(c VARCHAR(16384));
ERROR 1074 (42000): Column length too big for column 'c' (max = 16383); use BLOB or TEXT instead
mysql> create table vchars_c_16383(c VARCHAR(16383));
Query OK, 0 rows affected (0.01 sec)
mysql> create table vchars_c_0(c VARCHAR(0));
Query OK, 0 rows affected (0.01 sec)

# 查看當(dāng)前表模式
mysql> show variables like "sql_mode";
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+---------------+-----------------------------------------------------------------------------------------------------------------------+|
?sql_mode ? ? ?| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 可以看到STRICT_TRANS_TABLES模式，為支持事務(wù)性的表開(kāi)啟嚴(yán)格sql模式，因?yàn)槲疫@邊使用的存儲(chǔ)引擎是innodb，因此會(huì)默認(rèn)開(kāi)啟嚴(yán)格sql模式
?# 截?cái)嗫崭竦膱?chǎng)景
mysql> insert into chars (c, vc) values('123456 ', '123456');
Query OK, 1 row affected (0.00 sec)
mysql> insert into chars (c, vc) values('123456', '123456 ');
Query OK, 1 row affected, 1 warning (0.00 sec)?
# 截?cái)嘧址膱?chǎng)景
mysql> insert into chars (c, vc) values('abcdef', 'abcdefg');
ERROR 1406 (22001): Data too long for column 'vc' at row 1
mysql> insert into chars (c, vc) values('abcdefg', 'abcdef');
ERROR 1406 (22001): Data too long for column 'c' at row 1
# 關(guān)閉嚴(yán)格sql模式
mysql> SET sql_mode = '';Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "sql_mode";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode ? ? ?| ? ? ? |
+---------------+-------+
1 row in set (0.00 sec)?
# 截?cái)嗫崭竦膱?chǎng)景
mysql> insert into chars (c, vc) values('abcdef ', 'abcdef');
Query OK, 1 row affected (0.00 sec)
mysql> insert into chars (c, vc) values('abcdef', 'abcdef ');
Query OK, 1 row affected, 1 warning (0.00 sec)
# 截?cái)嘧址膱?chǎng)景
mysql> insert into chars (c, vc) values('abcdef', 'abcdefg');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into chars (c, vc) values('abcdefg', 'abcdef');
Query OK, 1 row affected, 1 warning (0.00 sec)

insert into chars (c, vc) values('2 ?', '2 ?');
mysql> select CONCAT('(', c, ')'), CONCAT('(', vc, ')') from chars where c = '2 ?';
Empty set (0.00 sec)
mysql> select CONCAT('(', c, ')'), CONCAT('(', vc, ')') from chars where c = '2';
+---------------------+----------------------+|?
CONCAT('(', c, ')') | CONCAT('(', vc, ')') |
+---------------------+----------------------+|
?(2) ? ? ? ? ? ? ? ? | (2 ?) ? ? ? ? ? ? ? ?|
+---------------------+----------------------+
1 row in set (0.00 sec)
mysql> select CONCAT('(', c, ')'), CONCAT('(', vc, ')') from chars where vc = '2';
Empty set (0.00 sec)
mysql> select CONCAT('(', c, ')'), CONCAT('(', vc, ')') from chars where vc = '2 ?';
+---------------------+----------------------+
| CONCAT('(', c, ')') | CONCAT('(', vc, ')') |
+---------------------+----------------------+
| (2) ? ? ? ? ? ? ? ? | (2 ?) ? ? ? ? ? ? ? ?|
+---------------------+----------------------+
1 row in set (0.00 sec)