2009-05-19--05-20 MySQL學習筆記02

1.整數類型

TINYINT 1字節
SMALLINT 2字節
MEDIUMINT 3字節
INT或INTEGER 4字節
BIGINT 8字節

整數類型后面圓括號中的數字代表該整型字段的顯示寬度，如果數字位不足就自動用空格填充，但這不會影響該字段的大小和可存儲的值的范圍。
UNSIGNED修飾符規定字段只保存正值；ZEROFILL修飾符規定用0來代替空格用于填補輸出值，使用它的字段也不能存儲負值。

[@more@]mysql> create table int_test
-> (
-> num1 tinyint,
-> num2 tinyint(3),
-> num3 tinyint unsigned,
-> num4 tinyint(3) zerofill
-> );
Query OK, 0 rows affected (0.12 sec)

mysql> desc int_test;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| num1 | tinyint(4) | YES | | NULL | |
| num2 | tinyint(3) | YES | | NULL | |
| num3 | tinyint(3) unsigned | YES | | NULL | |
| num4 | tinyint(3) unsigned zerofill | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> insert into int_test values(1,1,1,1);
Query OK, 1 row affected (0.43 sec)

mysql> insert into int_test values(-1,-1,-1,-1);
ERROR 1264 (22003): Out of range value for column 'num3' at row 1
mysql> insert into int_test values(-1,-1,1,-1);
ERROR 1264 (22003): Out of range value for column 'num4' at row 1
mysql> insert into int_test values(127,127,127,127);
Query OK, 1 row affected (0.59 sec)

mysql> insert into int_test values(128,128,128,128);
ERROR 1264 (22003): Out of range value for column 'num1' at row 1
mysql> insert into int_test values(127,127,128,127);
Query OK, 1 row affected (0.04 sec)

mysql> select * from int_test;
+------+------+------+------+
| num1 | num2 | num3 | num4 |
+------+------+------+------+
| 1 | 1 | 1 | 001 |
| 127 | 127 | 127 | 127 |
| 127 | 127 | 128 | 127 |
+------+------+------+------+
3 rows in set (0.03 sec)


2.浮點類型

FLOAT 4字節
DOUBLE或REAL DOUBLE PRECISION 8字節
DECIMAL或DEC NUMERIC 對DECIMAL(M,D)，如果M>D，為M+2，否則為D+2。

浮點類型后面圓括號中的兩個數字分別為一個顯示寬度指示器和一個小數點指示器。
UNSIGNED和ZEROFILL修飾符同樣可以用于浮點類型，效果與用于整數類型時類似。
MySQL以二進制格式保存DECIMAL數據類型，用于保存必須為確切精度的值。

mysql> create table float_test
-> (
-> num1 float,
-> num2 float(5,2),
-> num3 float unsigned,
-> num4 float(5,2) zerofill,
-> num5 decimal,
-> num6 decimal(5,2)
-> );
Query OK, 0 rows affected (0.58 sec)

mysql> desc float_test;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| num1 | float | YES | | NULL | |
| num2 | float(5,2) | YES | | NULL | |
| num3 | float unsigned | YES | | NULL | |
| num4 | float(5,2) unsigned zerofill | YES | | NULL | |
| num5 | decimal(10,0) | YES | | NULL | |
| num6 | decimal(5,2) | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
6 rows in set (0.05 sec)

mysql> insert into float_test values (123.321, 123.321, 123.321, 123.321, 123.32
1, 123.321);
Query OK, 1 row affected, 2 warnings (0.04 sec)

mysql> insert into float_test values (-123.3, -123.3, -123.3, -123.3, -123.3, -1
23.3);
ERROR 1264 (22003): Out of range value for column 'num3' at row 1
mysql> insert into float_test values (-123.3, -123.3, 123.3, -123.3, -123.3, -12
3.3);
ERROR 1264 (22003): Out of range value for column 'num4' at row 1
mysql> insert into float_test values (-123.3, -123.3, 123.3, 123.3, -123.3, -123
.3);
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into float_test values (123.456, 123.456, 123.456, 123.456, 123.45
6, 123.456);
Query OK, 1 row affected, 2 warnings (0.03 sec)

mysql> insert into float_test values (-123.456, -123.456, 123.456, 123.456, -123
.456, -123.456);
Query OK, 1 row affected, 2 warnings (0.03 sec)

mysql> select * from float_test;
+----------+---------+---------+--------+------+---------+
| num1 | num2 | num3 | num4 | num5 | num6 |
+----------+---------+---------+--------+------+---------+
| 123.321 | 123.32 | 123.321 | 123.32 | 123 | 123.32 |
| -123.3 | -123.30 | 123.3 | 123.30 | -123 | -123.30 |
| 123.456 | 123.46 | 123.456 | 123.46 | 123 | 123.46 |
| -123.456 | -123.46 | 123.456 | 123.46 | -123 | -123.46 |
+----------+---------+---------+--------+------+---------+
4 rows in set (0.02 sec)


3.字符串類型

CHAR 0-255字節
VARCHAR 0-255字節
TINYBLOB 0-255字節
TINYTEXT 0-255字節
BLOB 0-65535字節
TEXT 0-65535字節
MEDIUMBLOB 0-16777215字節
MEDIUMTEXT 0-16777215字節
LONGBLOB 0-294967295字節
LONGTEXT 0-294967295字節

CHAR類型用于定長字符串，其后面圓括號中的數字指定要存儲的值的長度，范圍為0到255。比指定長度小的值會用空格適當填補，比指定長度大的值將被自動截短。
VARCHAR類型用于變長字符串，其后面圓括號中的數字指定存儲的值的最大長度（必須指定），范圍為0到255。比指定最大長度小的值會以其實際大小存儲，不會用空格填充，比指定最大長度大的值將被自動截短。
CHAR和VARCHAR類型的值默認情況下不區分大小寫，可以使用BINARY修飾符改變這一點。

mysql> create table char_test
-> (
-> string1 char,
-> string2 char(5),
-> string3 char(5) binary,
-> string4 varchar(5),
-> string5 varchar(5) binary
-> );
Query OK, 0 rows affected (0.49 sec)

mysql> desc char_test;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| string1 | char(1) | YES | | NULL | |
| string2 | char(5) | YES | | NULL | |
| string3 | char(5) | YES | | NULL | |
| string4 | varchar(5) | YES | | NULL | |
| string5 | varchar(5) | YES | | NULL | |
+---------+------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> insert into char_test values ('abc', 'abc', 'abc', 'abc', 'abc');
ERROR 1406 (22001): Data too long for column 'string1' at row 1

這里發生了錯誤，要插入的記錄第一個字段的長度超過了string1字段默認指定的長度1，這是因為服務器運行在嚴格模式。查看my.ini中的相關設置：

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

將這部分修改為：

# Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

重啟服務器，再重新嘗試：

mysql> insert into char_test values ('abc', 'abc', 'abc', 'abc', 'abc');
Query OK, 1 row affected, 1 warning (0.05 sec)

這次記錄順利插入char_test表。繼續之前的實驗：

mysql> insert into char_test values ('abc', 'abc', 'abc', 'abc', 'abc');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> insert into char_test values ('abcde', 'abcde', 'abcde', 'abcde', 'abcde'
);
Query OK, 1 row affected, 1 warning (0.44 sec)

mysql> select * from char_test;
+---------+---------+---------+---------+---------+
| string1 | string2 | string3 | string4 | string5 |
+---------+---------+---------+---------+---------+
| a | abc | abc | abc | abc |
| a | abcde | abcde | abcde | abcde |
+---------+---------+---------+---------+---------+
2 rows in set (0.00 sec)

mysql> select * from char_test where string2 = 'ABC';
+---------+---------+---------+---------+---------+
| string1 | string2 | string3 | string4 | string5 |
+---------+---------+---------+---------+---------+
| a | abc | abc | abc | abc |
+---------+---------+---------+---------+---------+
1 row in set (0.41 sec)

mysql> select * from char_test where string3 = 'ABC';
Empty set (0.00 sec)

mysql> select * from char_test where string4 = 'ABC';
+---------+---------+---------+---------+---------+
| string1 | string2 | string3 | string4 | string5 |
+---------+---------+---------+---------+---------+
| a | abc | abc | abc | abc |
+---------+---------+---------+---------+---------+
1 row in set (0.00 sec)

mysql> select * from char_test where string5 = 'ABC';
Empty set (0.00 sec)


BLOB和TEXT（TEXT類型是不區分大小寫的BLOB類型?）類型用于存儲比較長的可變長字符串，但不能像VARCHAR類型那樣為它們指定最大長度。每一種BLOG或TEXT類型的最大長度都是固定的，超過的值將被自動截短。如果不確定應該使用哪種BLOB或TEXT類型，可以在BLOB或TEXT（其它的不可以）后面加一個圓括號，并在括號中輸入字符串的最大長度，這樣系統會自動選擇合適的類型。

mysql> create table blob_text_test
-> (
-> string1 blob(255),
-> string2 text(255),
-> string3 blob(65535),
-> string4 text(65535),
-> string5 blob(16777215),
-> string6 text(16777215),
-> string7 blob(294967295),
-> string8 text(294967295)
-> );
Query OK, 0 rows affected (0.52 sec)

mysql> desc blob_text_test
-> ;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| string1 | tinyblob | YES | | NULL | |
| string2 | tinytext | YES | | NULL | |
| string3 | blob | YES | | NULL | |
| string4 | text | YES | | NULL | |
| string5 | mediumblob | YES | | NULL | |
| string6 | mediumtext | YES | | NULL | |
| string7 | longblob | YES | | NULL | |
| string8 | longtext | YES | | NULL | |
+---------+------------+------+-----+---------+-------+
8 rows in set (0.03 sec)


4.日期和時間類型

類型 大小 范圍 格式 用途
DATE 3 1000-01-01 ~ 9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59' ~ '838:59:59' HH:MM:SS 時間值或持續時間
YEAR 1 1901 ~ 2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和時間值
TIMESTAMP 8 1970-01-01 00:00:00 ~ 2037年的某個時候 YYYYMMDDHHMMSS 混合日期和時間，時間戳

這些類型可以描述為字符串或不帶分隔符的整數序列，如果描述為字符串，應該按照上表“范圍”列中的格式。

mysql> create table date_time_test
-> (
-> dt1 date,
-> dt2 time,
-> dt3 year,
-> dt4 datetime,
-> dt5 timestamp
-> );
Query OK, 0 rows affected (0.49 sec)

mysql> insert into date_time_test values ('2008-05-12', '014:28:57', '2008', '20
08-05-12 14:28:57', '2008-05-12 14:28:57');
Query OK, 1 row affected (0.03 sec)

mysql> insert into date_time_test values (20080512, 0142857, 2008, 2008051214285
7, 20080512142857);
Query OK, 1 row affected (0.03 sec)

mysql> select * from date_time_test;
+------------+----------+------+---------------------+---------------------+
| dt1 | dt2 | dt3 | dt4 | dt5 |
+------------+----------+------+---------------------+---------------------+
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
+------------+----------+------+---------------------+---------------------+
2 rows in set (0.00 sec)

可以看到，使用字符串和整數序列描述方式插入的記錄的是完全相同的。當輸入的值格式不標準時，MySQL盡可能地去“理解”它們：

mysql> insert into date_time_test values ('2008-5-12', '3:4:5', '08', '2008-5-12
3:4:5', '08-5-12 3:4:5');
Query OK, 1 row affected (0.04 sec)

mysql> insert into date_time_test values (080512, 142857, 008, 2008512345, 08051
21428);
Query OK, 1 row affected, 1 warning (0.44 sec)

mysql> insert into date_time_test values ('2008-05', 1428, '8', '8-5-12 14:28',
'2008-05 14:28');
Query OK, 1 row affected, 2 warnings (0.04 sec)

mysql> select * from date_time_test;
+------------+----------+------+---------------------+---------------------+
| dt1 | dt2 | dt3 | dt4 | dt5 |
+------------+----------+------+---------------------+---------------------+
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
| 2008-05-12 | 03:04:05 | 2008 | 2008-05-12 03:04:05 | 2008-05-12 03:04:05 |
| 2008-05-12 | 14:28:57 | 2008 | 0000-00-00 00:00:00 | 2000-08-05 12:14:28 |
| 0000-00-00 | 00:14:28 | 2008 | 0008-05-12 14:28:00 | 0000-00-00 00:00:00 |
+------------+----------+------+---------------------+---------------------+
5 rows in set (0.00 sec)

對于TIMESTAMP類型，當被指定為NULL，或作為記錄中的第一個字段而未被明確指定值時，MySQL將會用當前的日期和時間自動填充它，而DATETIME類型則不行。

mysql> create table ts_dt_test
-> (
-> dt1 timestamp,
-> dt2 timestamp,
-> dt3 datetime
-> );
Query OK, 0 rows affected (0.52 sec)

mysql> desc ts_dt_test;
+-------+-----------+------+-----+---------------------+------------------------
-----+
| Field | Type | Null | Key | Default | Extra
|
+-------+-----------+------+-----+---------------------+------------------------
-----+
| dt1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMES
TAMP |
| dt2 | timestamp | NO | | 0000-00-00 00:00:00 |
|
| dt3 | datetime | YES | | NULL |
|
+-------+-----------+------+-----+---------------------+------------------------
-----+
3 rows in set (0.01 sec)


mysql> insert into ts_dt_test values (NULL, NULL, NULL);
Query OK, 1 row affected (0.03 sec)

mysql> insert into ts_dt_test (dt3) values (NOW());
Query OK, 1 row affected (0.06 sec)

mysql> insert into ts_dt_test (dt2, dt3) values (NOW(), NOW());
Query OK, 1 row affected (0.03 sec)

mysql> select * from ts_dt_test;
+---------------------+---------------------+---------------------+
| dt1 | dt2 | dt3 |
+---------------------+---------------------+---------------------+
| 2009-05-20 15:09:20 | 2009-05-20 15:09:20 | NULL |
| 2009-05-20 15:10:20 | 0000-00-00 00:00:00 | 2009-05-20 15:10:20 |
| 2009-05-20 15:10:59 | 2009-05-20 15:10:59 | 2009-05-20 15:10:59 |
+---------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

最后，嘗試插入不合法的日期和時間值，MySQL會自動將其置0。

mysql> insert into ts_dt_test values ('2009-02-30', '2009-05-32', '2009-01-01 24
:00:00');
Query OK, 1 row affected, 3 warnings (0.03 sec)

mysql> select * from ts_dt_test;
+---------------------+---------------------+---------------------+
| dt1 | dt2 | dt3 |
+---------------------+---------------------+---------------------+
| 2009-05-20 15:09:20 | 2009-05-20 15:09:20 | NULL |
| 2009-05-20 15:10:20 | 0000-00-00 00:00:00 | 2009-05-20 15:10:20 |
| 2009-05-20 15:10:59 | 2009-05-20 15:10:59 | 2009-05-20 15:10:59 |
| 2009-05-20 15:26:44 | 2009-05-20 15:20:11 | NULL |
| 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)


5.復合類型

MySQL有兩個復合類型ENUM和SET，它們擴展了SQL的規范。ENUM類型必須從一個允許值的集合中選擇單個值，而SET類型可以從允許值的集合中選擇任意多個值。詳細用法這里不再贅述。