MySQL 欄位類型總結

MySQL中各種欄位的取值範圍

時間:2005-09-29 20:59:14  來源:techtarget.com.cn  作者:techtarget.com.cn

 

　　TINYINT

　　-128 - 127

　　TINYINT UNSIGNED

　　0 - 255

　　SMALLINT

　　-32768 - 32767

　　SMALLINT UNSIGNED

　　0 - 65535

　　MEDIUMINT

　　-8388608 - 8388607

　　MEDIUMINT UNSIGNED

　　0 - 16777215

　　INT 或 INTEGER

　　-2147483648 - 2147483647

　　INT UNSIGNED 或 INTEGER UNSIGNED

　　0 - 4294967295

　　BIGINT

　　-9223372036854775808 - 9223372036854775807

　　BIGINT UNSIGNED

　　0 - 18446744073709551615

　　FLOAT

　　-3.402823466E+38 - -1.175494351E-38

　　0

　　1.175494351E-38 - 3.402823466E+38

　　DOUBLE 或 DOUBLE PRECISION 或 REAL

　　-1.7976931348623157E+308 - -2.2250738585072014E-308

　　0

　　2.2250738585072014E-308 - 1.7976931348623157E+308

　　DECIMAL[(M,[D])] 或 NUMERIC(M,D)

　　由M(整個數位長度,包括小數點,小數點左邊的位元,小數點右邊的位元,但不包括負號)和D(小數點右邊的位元)來決定,M預設為10,D預設為0

　　DATE

　　1000-01-01 - 9999-12-31

　　DATETIME

　　1000-01-01 00:00:00 - 9999-12-31 23:59:59

　　TIMESTAMP

　　1970-01-01 00:00:00 - 2037年的某天(具體是哪天我也不知道,呵呵)

　　TIME

　　-838:59:59' to 838:59:59

　　YEAR[(2|4)]

　　預設為4位格式,4位格式取值範圍為1901 - 2155,0000,2位格式取值範圍為70-69(1970-2069)

　　CHAR(M) [BINARY] 或 NCHAR(M) [BINARY]

　　M的範圍為1 - 255,如果沒有BINARY項,則不分大小寫,NCHAR表示使用預設的字元集.在資料庫中以空格補足,但在取出來時末尾的空格將自動去掉.

　　[NATIONAL] VARCHAR(M) [BINARY]

　　M的範圍為1 - 255.在資料庫中末尾的空格將自動去掉.

　　TINYBLOB 或 TINYTEXT

　　255(2^8-1)個字元

　　BLOB 或 TEXT

　　65535(2^16-1)個字元

　　MEDIUMBLOB 或 MEDIUMTEXT

　　16777215 (2^24-1)個字元

　　LONGBLOB 或 LONGTEXT

　　4294967295 (2^32-1)個字元

　　ENUM('value1','value2',...)

　　可以總共有65535個不同的值

　　SET('value1','value2',...)

 

 

 

-------------------------------------------------------------------

 

 

 

讓 MySQL 的 TimeStamp 列不要自動更新

 

MySQL 有一種 timestamp 列類型，可以自動為該列插入目前時間戳，當 Update 語句執行時，該時間戳記也會自動更新。但有的時候，並不希望在更新時該值也自動更新，那該怎麼辦呢？

其實也很簡單，timestamp 自身支援這種設定。

當建立 timestamp 列時，一般的語句為：

create table tablename ( datecolname timestamp not null , ……..）

其實該語句的完整語句為：

create table tablename (datecolname timestamp not null default current_timestamp on update current_timetamp, ……..)

如果你想讓行值發生變化時時間戳記不要自動更新，可以使用以下語句：

create table tablename(datecolname timestamp not null default current_timestamp, ……..)

問題的關鍵就在於有 default current_timestamp 語句而沒有 on update current_timestamp 語句。

在 MySQL Query Browser 中建立表結構時，如果不想讓 timestamp 自動更新，記得一定要手動輸入 default 為 current_timestamp ，這樣 MySQL Query Browser 就會自動產生合適的 SQL 陳述式來建立表結構了。

 

 

------------------------------------------------

 

mysql TIMESTAMP列類型詳解  

mysql目前不支援列的default 為函數的形式,
如達到你某列的預設值為當前更新日期與時間的功能,
你可以使用timestamp列類型
下面就詳細說明timestamp列類型

timestamp列類型
timestamp值可以從1970的某時的開始一直到2037年，精度為一秒，其值作為數字顯示。
timestamp值顯示尺寸的格式如下表所示：
：
+---------------+----------------+
| 列類型        | 顯示格式       |
| timestamp(14) | yyyymmddhhmmss | 
| timestamp(12) | yymmddhhmmss   |
| timestamp(10) | yymmddhhmm     |
| timestamp(8)  | yyyymmdd       |
| timestamp(6)  | yymmdd         |
| timestamp(4)  | yymm           |
| timestamp(2)  | yy             |
+---------------+----------------+
“完整”timestamp格式是14位，但timestamp列也可以用更短的顯示尺寸創造
最常見的顯示尺寸是6、8、12、和14。
你可以在建立表時指定一個任意的顯示尺寸，但是定義列長為0或比14大均會被強制定義為列長14。
列長在從1～13範圍的奇數值尺寸均被強製為下一個更大的偶數。

列如：
定義欄位長度     強制欄位長度
timestamp(0) ->  timestamp(14)
timestamp(15)->  timestamp(14)
timestamp(1) ->  timestamp(2)
timestamp(5) ->  timestamp(6)

所有的timestamp列都有同樣的儲存大小，
使用被指定的時期時間值的完整精度（14位）儲存合法的值不考慮顯示尺寸。
不合法的日期，將會被強製為0儲存
這有幾個含意：
    1、雖然你建表時定義了列timestamp(8)，但在你進行資料插入與更新時timestamp列
           實際上儲存了14位的資料（包括年月日時分秒），
           只不過在你進行查詢時mysql返回給你的是8位的年月日資料。
           如果你使用alter table拓寬一個狹窄的timestamp列，以前被“隱蔽”的資訊將被顯示。
    2、同樣，縮小一個timestamp列不會導致資訊失去，除了感覺上值在顯示時，較少的資訊被顯示出。
    3、儘管timestamp值被儲存為完整精度，直接操作儲存值的唯一函數是unix_timestamp()；
           由於mysql返回timestamp列的列值是進過格式化後的檢索的值，
           這意味著你可能不能使用某些函數來操作timestamp列（例如hour()或second()），
           除非timestamp值的相關部分被包含在格式化的值中。
           例如，一個timestamp列只有被定義為timestamp(10)以上時，timestamp列的hh部分才會被顯示，
           因此在更短的timestamp值上使用hour()會產生一個不可預知的結果。
    4、不合法timestamp值被變換到適當類型的“零”值(00000000000000)。（datetime,date亦然）       

你可以使用下列語句來驗證：
create table test ('id' int (3) unsigned auto_increment, 'date1' timestamp (8) primary key('id'));
insert into test set id = 1;
select * from test;
+----+----------------+
| id | date1          |
+----+----------------+
|  1 | 20021114       |
+----+----------------+
alter table test change 'date1' 'date1' timestamp(14);
select * from test;
+----+----------------+
| id | date1          |
+----+----------------+
|  1 | 20021114093723 |
+----+----------------+

 

你可以使用timestamp列類型自動地用當前的日期和時間標記insert或update的操作。
如果你有多個timestamp列，只有第一個自動更新。
自動更新第一個timestamp列在下列任何條件下發生：

   1、列值沒有明確地在一個insert或load data infile語句中指定。
   2、列值沒有明確地在一個update語句中指定且另外一些的列改變值。
       （注意一個update設定一個列為它已經有的值，
        這將不引起timestamp列被更新，
        因為如果你設定一個列為它當前的值，mysql為了效率而忽略更改。）
   3、你明確地設定timestamp列為null.
   4、除第一個以外的timestamp列也可以設定到當前的日期和時間，只要將列設為null，或now()。

create table test (
     'id' int (3) unsigned auto_increment,
     'date1' timestamp (14),
     'date2' timestamp (14),
      primary key('id')
      );

insert into test (id, date1, date2) values (1, null, null);
insert into test set id= 2;
+----+----------------+----------------+
| id | date1          | date2          |
+----+----------------+----------------+
|  1 | 20021114093723 | 20021114093723 |
|  2 | 20021114093724 | 00000000000000 |
+----+----------------+----------------+
->第一條指令因設date1、date2為null,所以date1、date2值均為目前時間
  第二條指令因沒有設date1、date2列值，第一個timestamp列date1為更新為目前時間，
    而二個timestamp列date2因日期不合法而變為“00000000000000”

update test set id= 3 where id=1;
+----+----------------+----------------+

Demo：

 

MYSQL中TIMESTAMP類型可以設定預設值，就像其他類型一樣。

1、自動UPDATE 和INSERT 到當前的時間：

表：

/*DDL Information For - test.t1*/

---------------------------------

Table   Create Table                                                                        

------  -------------------------------------------------------------------------------------

t1      CREATE TABLE `t1` (                                                                 

          `p_c` int(11) NOT NULL,                                                           

          `p_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 

        ) ENGINE=InnoDB DEFAULT CHARSET=gb2312                                              

資料：

1    2007-10-08 11:53:35

2    2007-10-08 11:54:00

insert into t1(p_c) select 3;

update t1 set p_c = 2 where p_c = 2;

資料：

1    2007-10-08 11:53:35

2    2007-10-08 12:00:37

3    2007-10-08 12:00:37

2、自動INSERT 到目前時間，不過不自動UPDATE。

表：

/*DDL Information For - test.t1*/

---------------------------------

Table   Create Table                                            

------  ---------------------------------------------------------

t1      CREATE TABLE `t1` (                                     

          `p_c` int(11) NOT NULL,                               

          `p_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 

        ) ENGINE=InnoDB DEFAULT CHARSET=gb2312                  

資料：

insert into t1(p_c) select 4;

update t1 set p_c = 3 where p_c = 3;

1    2007-10-08 11:53:35

2    2007-10-08 12:00:37

3    2007-10-08 12:00:37

4    2007-10-08 12:05:19

3、一個表中不能有兩個欄位預設值是目前時間，否則就會出錯。不過其他的可以。

表：

/*DDL Information For - test.t1*/

---------------------------------

Table   Create Table                                                  

------  ---------------------------------------------------------------

t1      CREATE TABLE `t1` (                                           

          `p_c` int(11) NOT NULL,                                     

          `p_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,      

          `p_timew2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' 

        ) ENGINE=InnoDB DEFAULT CHARSET=gb2312                        

資料：

1    2007-10-08 11:53:35    0000-00-00 00:00:00

2    2007-10-08 12:00:37    0000-00-00 00:00:00

3    2007-10-08 12:00:37    0000-00-00 00:00:00

4    2007-10-08 12:05:19    0000-00-00 00:00:00