# 創(chuàng)建測(cè)試表 字符集是 utf8
mysql> show create table tb_text\G
*************************** 1. row ***************************
       Table: tb_text
Create 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

# 創(chuàng)建索引測(cè)試 發(fā)現(xiàn)text類(lèi)型必須指定前綴長(zhǎng)度
mysql> alter table tb_text add index idx_a (a);
ERROR 1170 (42000): BLOB/TEXT column 'a' used in key specification without a key length
mysql> alter table tb_text add index idx_b (b); 
ERROR 1170 (42000): BLOB/TEXT column 'b' used in key specification without a key length
mysql> alter table tb_text add index idx_c (c);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table tb_text add index idx_b (b(10));
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 插入數(shù)據(jù)測(cè)試（repeat函數(shù)用于生成重復(fù)數(shù)據(jù)）
# 正常插入
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)
# 插入英文字符超標(biāo)
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)
# 插入中文超標(biāo)
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)
# 查看數(shù)據(jù) 發(fā)現(xiàn)數(shù)據(jù)有所截取 tinytext 類(lèi)型最多存儲(chǔ)255字節(jié)數(shù)據(jù)
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)