mysql enum

时间：2010-04-12 来源：ubuntuer

如果确定字符串只能出现少数的几种字符串,那么就可以使用枚举...可以节省存储空间。MySQL枚举可以存储
65535个字符串,但是不建议枚举这么多.... mysql> create table enum_test(num ENUM('kenthy','jimmy','jeremy') NOT NULL, str VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.01 sec) mysql> insert into enum_test values('jimmy','jimmy'),('kenthy','kenthy'),('jeremy','jeremy');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from enum_test;
+--------+--------+
| num    | str    |
+--------+--------+
| jimmy | jimmy |
| kenthy | kenthy |
| jeremy | jeremy |
+--------+--------+ mysql> select num+0 str+0 from enum_test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+0 from enum_test' at line 1
mysql> select num+0,str+0 from enum_test;
+-------+-------+
| num+0 | str+0 |
+-------+-------+
|     2 |     0 |
|     1 |     0 |
|     3 |     0 |
+-------+-------+
3 rows in set (0.00 sec) mysql> desc enum_test;
+-------+---------------------------------+------+-----+---------+-------+
| Field | Type                            | Null | Key | Default | Extra |
+-------+---------------------------------+------+-----+---------+-------+
| num   | enum('kenthy','jimmy','jeremy') | NO   |     | NULL    |       |
| str   | varchar(10)                     | NO   |     | NULL    |       |
+-------+---------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec) mysql> select num+0,str from enum_test order by num;
+-------+--------+
| num+0 | str    |
+-------+--------+
|     1 | kenthy |
|     2 | jimmy |
|     3 | jeremy |
+-------+--------+
3 rows in set (0.00 sec) mysql> select num+0,str from enum_test order by str;
+-------+--------+
| num+0 | str    |
+-------+--------+
|     3 | jeremy |
|     2 | jimmy |
|     1 | kenthy |
+-------+--------+
3 rows in set (0.00 sec)
其实enum与bit最好的实现方法是在程序中实现,数据库中都只保存一个简单int or smallint值...
程序自己去转换去实现...