# 创建测试表 字符集是 utf8mysql> show create table tb_text\G*************************** 1. row *************************** Table: tb_textCreate Table: CREATE TABLE `tb_text` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `a` tinytext, `b` text, `c` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
# 创建索引测试 发现text类型必须指定前缀长度mysql> alter table tb_text add index idx_a (a);ERROR 1170 (42000): BLOB/TEXT column 'a' used in key specification without a key lengthmysql> alter table tb_text add index idx_b (b); ERROR 1170 (42000): BLOB/TEXT column 'b' used in key specification without a key lengthmysql> alter table tb_text add index idx_c (c);Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table tb_text add index idx_b (b(10));Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0
# 插入数据测试（repeat函数用于生成重复数据）# 正常插入mysql> insert into tb_text (a,b,c) values (repeat('hello',3),repeat('hello',3),repeat('hello',3));Query OK, 1 row affected (0.01 sec)# 插入英文字符超标mysql> insert into tb_text (a) values (repeat('hello',52));Query OK, 1 row affected, 1 warning (0.01 sec)mysql> show warnings;+---------+------+----------------------------------------+| Level | Code | Message |+---------+------+----------------------------------------+| Warning | 1265 | Data truncated for column 'a' at row 1 |+---------+------+----------------------------------------+1 row in set (0.00 sec)# 插入中文超标mysql> insert into tb_text (a) values (repeat('你好',100));Query OK, 1 row affected, 1 warning (0.02 sec)mysql> show warnings;+---------+------+----------------------------------------+| Level | Code | Message |+---------+------+----------------------------------------+| Warning | 1265 | Data truncated for column 'a' at row 1 |+---------+------+----------------------------------------+1 row in set (0.00 sec)# 查看数据 发现数据有所截取 tinytext 类型最多存储255字节数据mysql> select * from tb_text;+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+-----------------+| id | a | b | c |+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+-----------------+| 1 | hellohellohello | hellohellohello | hellohellohello || 2 | hellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohellohello | NULL | NULL || 3 | 你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你好你 | NULL | NULL |+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+-----------------+3 rows in set (0.00 sec)
评论