[原創] sql mode與 enum 型別

yxyup發表於2008-07-02
SQL型別
 
1.sql mode簡介
 
在Mysql中,sql mode可以用來解決以下問題
(1).通過設定不同的sql mode,可以在不同嚴格程式進行資料校驗.有效地保證了資料準確性.
(2).通過設定sql mode為ANSI模式,來保證大多數SQL符合標準SQL的語法,這樣在不同資料庫之間遷移時,不需要對業務    修改太多.
 
在Mysql 5.0以下,查詢預設的sql mode(sql mode引數)有:real_as_float,pipes_as_concat,ansi_quotes,gnore_space和ANSI。在這些模式下可以插入超過欄位定義長度的資料，或是在欄位中沒有定義的元素資料(如,enum)。不過在插入後會有一個warning(可以用 show warnings來檢視)。
 
可以通過設定sql mode為STRICT_TRANS_TABLES(嚴格模式)來實現資料的嚴格校檢,使錯誤資料不能插入，從而保證資料準確性。
 
TRADITIONAL模式也屬於嚴格模式，同樣可以實現嚴格校檢,使錯誤資料不能插入，從而保證資料準確性。不過在這種模式MAX(X,0)返回的結果是NULL，所以在包含有MAX的運算中根據實際情況設定好sql mode.
 
2.ENUM簡介
 
ENUM是一個字串物件，其值來自表建立時在列規定中顯式列舉的一列值。
在某些情況下，ENUM值也可以為空字串('')或NULL：
如果你將一個非法值插入ENUM(也就是說，允許的值列之外的字串)，將插入空字串以作為特殊錯誤值。該字串與“普通”空字串不同，該字串有數值值0。
如果將ENUM列宣告為允許NULL，NULL值則為該列的一個有效值，並且 預設值為NULL。如果ENUM列被宣告為NOT NULL，其預設值為允許的值列的第1個元素。
 
每個列舉值有一個索引：
 
來自列規定的允許的值列中的值從1開始編號。
空字串錯誤值的索引值是0。這說明你可以使用下面的SELECT語句來找出分配了非法ENUM值的行：
select * from test where browsertype=0;
 
NULL值的索引是NULL。
 
例如，定義為ENUM的列('one'，'two'，'three')可以有下面所示任何值。還顯示了每個值的索引：

值

索引

NULL

NULL

''

0

'one'

1

'two'

2

'three'

3

 
列舉最多可以有65,535個元素。
當建立表時，ENUM成員值的尾部空格將自動被刪除。
當檢索時，儲存在ENUM列的值使用列定義中所使用的大小寫來顯示。請注意可以為ENUM列分配字符集和 校對規則。對於二進位制或大小寫敏感的校對規則，當為列分配值時應考慮大小寫。
 
3.舉列測試
 
(1)sql mode為ANSI
 
mysql> create table test(id bigint(20) auto_increment primary key,
    -> browsertype enum('ie','firefox','other')
    -> );
Query OK, 0 rows affected (0.10 sec)
 
mysql> desc test;
+-------------+------------------------------+------+-----+---------+----------------+
| Field       | Type                         | Null | Key | Default | Extra          |
+-------------+------------------------------+------+-----+---------+----------------+
| id          | bigint(20)                   | NO   | PRI | NULL    | auto_increment |
| browsertype | enum('ie','firefox','other') | YES  |     | NULL    |                |
+-------------+------------------------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)
 
mysql> set session sql_mode='ANSI';
Query OK, 0 rows affected (0.14 sec)
 
mysql> insert into test(browsertype) values('ie') ;
Query OK, 1 row affected (0.07 sec)
 
mysql> insert into test(browsertype) values('maxthon') ;
Query OK, 1 row affected (0.03 sec)
 
mysql> show warnings;(資料雖然成功insert,但有warning)
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1265 | Data truncated for column 'browsertype' at row 1 |
+---------+------+--------------------------------------------------+
1 row in set (0.01 sec)
 
mysql> select * from test;
+----+-------------+
| id | browsertype |
+----+-------------+
|  1 | ie          |
|  2 |             |
+----+-------------+
2 rows in set (0.01 sec)
 
mysql> select * from test where browsertype=0;(空字串錯誤值的索引值是0。SELECT可找出分配了非法ENUM值的行)
+----+-------------+
| id | browsertype |
+----+-------------+
|  2 |             |
+----+-------------+
1 row in set (0.04 sec)
(2)使用嚴格模式(STRICT_TRANS_TABLES)
 
mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)
 
mysql> select * from test;
+----+-------------+
| id | browsertype |
+----+-------------+
|  1 | ie          |
|  2 |             |
+----+-------------+
2 rows in set (0.00 sec)
 
mysql> insert into test(browsertype) values('maxthon') ;
ERROR 1265: Data truncated for column 'browsertype' at row 1

mysql> insert into test(browsertype) values('firefox') ;
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from test;
+----+-------------+
| id | browsertype |
+----+-------------+
|  1 | ie          |
|  2 |             |
|  3 | firefox     |
+----+-------------+
3 rows in set (0.00 sec)
 
(3) TRADITIONAL模式
 
mysql> create table t11 (i int);
Query OK, 0 rows affected (0.02 sec)
 
mysql> set sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into t11 values(9%4);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t11;
+------+
| i    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
 
mysql> set sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into t11 values(9%0);
ERROR 1365: Division by 0

mysql> show warnings;
+-------+------+---------------+
| Level | Code | Message       |
+-------+------+---------------+
| Error | 1365 | Division by 0 |
+-------+------+---------------+
1 row in set (0.02 sec)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7364032/viewspace-368907/，如需轉載，請註明出處，否則將追究法律責任。

相關文章