大家都知道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
不过这对磁盘临时表没用。