关于varchar和text的一点点区别

大家都知道varchar(N)当N大于255的时候mysql内部会转成text类型进行存储，所有很多人只要存储的字符串大于255都不加思考的就随便使用，其实这里还有一个区别就是

查询当中是否使用临时表的问题，请看如下测试：

字段说明：

ref   varchar(1000) , org_id int ,id int 

测试语句：

show status like '%tmp%';select id   from ticket group by org_id order by ref limit 1,1 ; show status like '%tmp%';           
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 8      |
| Created_tmp_files             | 6      |
| Created_tmp_tables        | 17     |
| Handler_tmp_update      | 43650  |
| Handler_tmp_write       | 463519 |
| Rows_tmp_read           | 234483 |
+-------------------------+--------+
6 rows in set (0.00 sec)

Empty set (0.14 sec)

+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 8      |
| Created_tmp_files             | 6      |
| Created_tmp_tables      | 18     |
| Handler_tmp_update      | 43650  |
| Handler_tmp_write       | 463520 |
| Rows_tmp_read           | 234491 |
+-------------------------+--------+
6 rows in set (0.00 sec)

从上面的结果可以看的出来查询产生了内部临时表，但是并没有出现磁盘临时表

现在：修改ref的数据类型为text

alter table ticket modify ref text not null;                      

Query OK, 56337 rows affected (5.96 sec)               
Records: 56337  Duplicates: 0  Warnings: 0

show status like '%tmp%';select id   from ticket group by org_id order by ref limit 1,1 ; show status like '%tmp%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 8      |
| Created_tmp_files             | 6      |
| Created_tmp_tables      | 18     |
| Handler_tmp_update      | 43650  |
| Handler_tmp_write       | 463520 |
| Rows_tmp_read           | 234497 |
+-------------------------+--------+
6 rows in set (0.00 sec)

Empty set (0.21 sec)

+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 9      |
| Created_tmp_files       | 6      |
| Created_tmp_tables      | 19     |
| Handler_tmp_update      | 43650  |
| Handler_tmp_write       | 463521 |
| Rows_tmp_read           | 234505 |
+-------------------------+--------+
6 rows in set (0.00 sec)

可见产生了磁盘临时表。

现在将ref字段放在select 列列表中 order by 字段 改为id再次进行测试

show status like '%tmp%';select ref   from ticket group by org_id order by id limit 1,1 ; show status like '%tmp%';     
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 9      |
| Created_tmp_files       | 6      |
| Created_tmp_tables      | 19    |
| Handler_tmp_update      | 43650  |
| Handler_tmp_write       | 463521 |
| Rows_tmp_read           | 234511 |
+-------------------------+--------+
6 rows in set (0.00 sec)

Empty set (0.16 sec)

+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 10     |
| Created_tmp_files       | 6      |
| Created_tmp_tables      | 20     |
| Handler_tmp_update      | 43650  |
| Handler_tmp_write       | 463522 |
| Rows_tmp_read           | 234519 |
+-------------------------+--------+
6 rows in set (0.00 sec)

再次将ref 改为 varchar(1000) 进行测试

alter table ticket modify ref varchar(1000) not null;                                        

 Query OK, 56337 rows affected (5.84 sec)               
Records: 56337  Duplicates: 0  Warnings: 0

(user:root  time: 15:35)[db: itop1011]show status like '%tmp%';select ref   from ticket group by org_id order by id limit 1,1 ; show status like '%tmp%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 10     |
| Created_tmp_files       | 6      |
| Created_tmp_tables      | 20     |
| Handler_tmp_update      | 43650  |
| Handler_tmp_write       | 463522 |
| Rows_tmp_read           | 234525 |
+-------------------------+--------+
6 rows in set (0.00 sec)

Empty set (0.17 sec)

+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 10     |
| Created_tmp_files       | 6      |
| Created_tmp_tables      | 21     |
| Handler_tmp_update      | 43650  |
| Handler_tmp_write       | 463523 |
| Rows_tmp_read           | 234533 |
+-------------------------+--------+
6 rows in set (0.00 sec)

产生了内部临时表但是没有产生磁盘临时表

现在将ref字段放在group by后面测试

show status like '%tmp%';select id    from ticket group by ref  order by id limit 10,1 ; show status like '%tmp%';           
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 16     |
| Created_tmp_files       | 6      |
| Created_tmp_tables      | 43     |
| Handler_tmp_update      | 43650  |
| Handler_tmp_write       | 576262 |
| Rows_tmp_read           | 234969 |
+-------------------------+--------+
6 rows in set (0.01 sec)


+------+
| id   |
+------+
| 6302 |
+------+
1 row in set (1.19 sec)


+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 17     |
| Created_tmp_files       | 6      |
| Created_tmp_tables      | 44     |
| Handler_tmp_update      | 43650  |
| Handler_tmp_write       | 632599 |
| Rows_tmp_read           | 290990 |
+-------------------------+--------+
6 rows in set (0.00 sec)

依然产生了磁盘临时表。

想必测试到此，已经很清楚说明了两者之间的小区别。

总结：

varchar 长度大于255的时候 一般情况来和text没有啥特别大的区别，但是当查询用到了临时表

（具体mysql如何使用内部临时表可见：http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html）

如果查询列出现了text字段或者order by或group by里出现了text字段，这时就会产生磁盘临时表。

可见在表设计的时候能用varchar还是尽量使用varchar ，特别是text 又不支持默认值(但支持not null 约束）。

ps: 如果想减少Created_tmp_tables 可适当增大tmp_table_size的值，该变量支持动态修改set [global] tmp_table_size = 64*1024*1024

   不过这对磁盘临时表没用。