MySQL 類型屬性及時間，日期類型

1.1類型屬性

資料類型具有兩個屬性：UNSIGNED和ZEROFILL，是否使用這兩個屬性對選擇資料類型有莫大的關系。

1.1.1 UNSIGNED

UNSIGNED 屬性：就是将數字類型無符号化。

例如，INT的類型的取值範圍是：-2,147,483,648 ~ 2,147,483,647; INT UNSIGINED的取值範圍就是：0 ~ 4,294,967,295。

看起來這是一個不錯的屬性選項，特别是對于主鍵是自增長的類型，因為一般來說，使用者都希望主鍵是非負數。

然而在實際使用中，UNSIGNED可能會帶來一些負面的影響，示例如下：

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>t (a </code><code>INT</code> <code>UNSIGNED, b </code><code>INT</code> <code>UNSIGNED);</code>

<code>#插入一條資料</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>t </code><code>SELECT</code> <code>1,2;</code>

<code>mysql> </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>t\G;</code>

目前看來都沒有問題，接着運作如下語句：

SELECT a - b FROM t

這時結果會是什麼呢？答案是什麼？ -1 ？答案是不确定的。可以是 -1，可以是很大的正值，還可能報錯。

<code>#如下是linux系統運作的結果</code>

<code>mysql> </code><code>SELECT</code> <code>a - b </code><code>FROM</code> <code>t;</code>

<code>ERROR 1690 (22003): </code><code>BIGINT</code> <code>UNSIGNED value </code><code>is</code> <code>out</code> <code>of</code> <code>range </code><code>in</code> <code>'(`test`.`t`.`a` - `test`.`t`.`b`)'</code>

這個錯誤看起來非常奇怪，提示BIGINT UNSIGNED 超出了範圍，但是我們定義表時采用的類型是INT UNSIGNED啊！

那麼怎麼的到-1的結果呢？隻要對SQL_MODE參數進行設定即可，例如：

<code>mysql> </code><code>SET</code> <code>sql_mode = </code><code>'NO_UNSIGNED_SUBTRACTION'</code><code>;</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.00 sec)</code>

其實就是SQL_MODE的設定影響其結果值。

UNSIGNED屬性總結：

筆者個人的看法是盡量不要使用UNSIGNED，因為可能帶來一些意想不到的結果。另外對于INT類型可能存放不了的資料，INT UNSIGINED同樣可能存放不了，與其如此，還不如在資料庫設計階段将INT提升為BIGINT。

1.1.2 ZEROFILL

ZEROFILL屬性非常有意思，更像是一個顯示的屬性。很多初學者往往對MySQL資料庫中數字類型後面的長度很迷茫。下面通過SHOW CREATE TABLE指令來看一下t表的建表語句。

<code>mysql> SHOW </code><code>CREATE</code> <code>TABLE</code> <code>t\G;</code>

<code>Table</code><code>: t</code>

<code>Create</code> <code>Table</code><code>: </code><code>CREATE</code> <code>TABLE</code> <code>`t` (</code>

<code>`a` </code><code>int</code><code>(10) unsigned </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>`b` </code><code>int</code><code>(10) unsigned </code><code>DEFAULT</code> <code>NULL</code>

<code>) ENGINE=InnoDB </code><code>DEFAULT</code> <code>CHARSET=latin1</code>

可以看到int(10)，這代表什麼意思呢？整形不就4個位元組嗎？這10又代表什麼呢？其實如果沒有ZEROFILL這個屬性，括号内的數字是毫無意思的。a和b列就是前面插入的資料，例如：

但是對列添加ZEROFILL屬性後，顯示的結果就有所不同了，例如對表t的a字段添加ZEROFILL屬性：

<code>mysql> </code><code>ALTER</code> <code>TABLE</code> <code>t CHANGE </code><code>COLUMN</code> <code>a a </code><code>int</code><code>(4) UNSIGNED ZEROFILL;</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.01 sec)</code>

<code>Records: 0 Duplicates: 0 Warnings: 0</code>

<code>#将預設的</code><code>int</code><code>(10)修改為</code><code>int</code><code>(4)，這時在進行查找操作：</code>

這次看到a的值有原來的1變為了0001，這就是ZEROFILL屬性的作用，如果寬度小于設定的寬度（這裡寬度為4），則自動填充0。要注意的是：這隻是最後顯示的結果。在MySQL中實際存儲的還是1。

2.1 日期和時間類型

MySQL資料庫中有五種與日期和時間有關的資料類型，其資料類型所占能用的空間對比圖如下：

類型

所占空間

DATETIME

8位元組

TIMESTAMP

4位元組

TIME

3位元組

DATE

YEAR

1位元組

2.1.1 DATETIME和DATE

DATETIME占用8位元組，是占用空間最多的一種日期類型。既顯示了日期又同時顯示了時間。

顯示格式為：‘YYYY-MM-DD HH:MM:SS'

表達日期範圍：'1000-01-01 00:00:00' to '9999-12-31 23:59:59'

DATE占用3位元組，僅顯示日期。

顯示格式為：'YYYY-MM-DD'

表達的日期範圍：'1000-01-01' to '9999-12-31'

在MySQL資料庫中，對日期和時間輸入格式的要求是非常寬松的，以下輸入都可以視為日期類型：

2011-01-01 00:01:10

2011/01/01 00+01+10

20110101000110

其中，最後一種類型中的“11”有些模棱兩可，MySQL資料庫将其視為2011還是1911呢？下面測試下：

<code>mysql> </code><code>select</code> <code>CAST</code><code>(</code><code>'11/01/01 00@01@10'</code> <code>AS</code> <code>DATETIME) </code><code>AS</code> <code>datetime\G;</code>

<code>datetime: 2011-01-01 00:01:10</code>

可以看到資料庫将其視為離現在最近的一個年份，這可能并不是一個非常好的習慣。如果沒有特别的條件和要求，還是在輸入時按照标準的'YYYY-MM-DD HH:MM:SS'格式來進行。

說明：MySQL的CAST()函數可用來擷取一個類型的值。格式：CAST(value AS type)

在MySQL5.5版本之前（包括5.5），資料庫的日期類型不能精确到微秒級别，任何的微妙數值都會被資料庫截斷，例如：

<code>mysql> </code><code>create</code> <code>table</code> <code>time_test (a DATETIME);</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>time_test </code><code>SELECT</code> <code>'2013-07-09 15:47:39.123456'</code><code>;</code>

<code>Query OK, 1 row affected (0.01 sec)</code>

<code>Records: 1 Duplicates: 0 Warnings: 0</code>

<code>mysql> </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>time_test\G;</code>

不過MySQL資料庫提供了函數MICROSECOND()來提取日期中的微妙值，例如：

<code>mysql> </code><code>SELECT</code> <code>MICROSECOND(</code><code>'2013-07-09 15:47:39.123456'</code><code>)\G;</code>

<code>MICROSECOND(</code><code>'2013-07-09 15:47:39.123456'</code><code>): 123456</code>

然而從MySQL5.6.4版本開始，MySQL增加了對秒的小數部分的支援，具體文法為：type_name(fsp)

其中type_name的類型可以是TIME，DATETIME和TIMESTAMP。fsp表示支援秒的小數部分的精度，最大為6，表示微妙；預設為0，表示沒有小數部分。對于時間函數，如CURTIME(),SYSDATE(),和UTC_TIMESTAMP()也增加了對fsp的支援，例如：

<code>#說明下面的例子MySQL的版本為：5.6.12</code>

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>time_test (a DATETIME(4));</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.04 sec)</code>

<code>Query OK, 1 row affected (0.00 sec)</code>

<code>mysql> </code><code>SELECT</code> <code>CURTIME(4) </code><code>AS</code> <code>TIME</code><code>\G;</code>

2.1.2 TIMESTAMP

TIMESTAMP和DATETIME顯示的結果是一樣的。

顯示格式：'YYYY-MM-DD HH:MM:SS'

表達日期範圍：'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

TIMESTAMP類型和DATETIME類型除了在顯示時間範圍上有所不同外，還有以下不同：

在建表時，列為TIMESTAMP的日期類型可以設定一個預設值，而DATETIME不行。

在更新表時，可以設定TIMESTAMP類型的列自動更新時間為目前時間。

特别說明：這兩種情況是在MySQL5.6.5之前有差別的，之後TIMESTAMP和DATETIME都可以自動初始化及Update。

首先我們來測下5.6.5版本之前後的一個預設設定時間的例子：

<code>#此資料庫的版本為：</code>

<code>mysql> </code><code>select</code> <code>version();</code>

<code>| version() |</code>

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>test_time</code>

<code>-> b DATETIME </code><code>DEFAULT</code> <code>CURRENT_TIMESTAMP</code><code>);</code>

<code>ERROR 1067 (42000): Invalid </code><code>default</code> <code>value </code><code>for</code> <code>'b'</code>

可以看出初始化b字段的值，會報非法的預設值。看在MySQL5.6.5之後的版本：

<code>| version() |</code>

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>test_time ( a </code><code>INT</code><code>, b DATETIME </code><code>DEFAULT</code> <code>CURRENT_TIMESTAMP</code><code>);</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.03 sec)</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>test_time (a) </code><code>VALUES</code> <code>(1);</code>

<code>mysql> </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>test_time\G;</code>

接着來看一個執行UPDATE的時更新為目前時間的例子：

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>t</code>

<code>-> b </code><code>TIMESTAMP</code> <code>ON</code> <code>UPDATE</code> <code>CURRENT_TIMESTAMP</code><code>);</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.09 sec)</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>t </code><code>SELECT</code> <code>1,</code><code>CURRENT_TIMESTAMP</code><code>;</code>

<code>#等待一段時間</code>

<code>mysql> </code><code>UPDATE</code> <code>t </code><code>SET</code> <code>a = 2;</code>

<code>Query OK, 1 row affected (0.02 sec)</code>

<code>Rows</code> <code>matched: 1 Changed: 1 Warnings: 0</code>

可以發現在執行UPDATE操作後，b列的時間由原來的16:48:18更新為了16:49:34。如果執行了UPDATE操作，而實際上行并沒有得到更新，那麼是不會更新b列的，例如：

<code>Rows</code> <code>matched: 1 Changed: 0 Warnings: 0</code>

可以看到執行UPDATE并沒有改變行的任何資料，顯示Changed: 0，故b列并不會進行相應的更新操作。

2.1.3 YEAR和TIME

YEAR類型占用1位元組，并且在定義時可以指定顯示的寬度為YEAR(4)或YEAR(2)。

對于YEAR(4)顯示年份的範圍：'1901' to '2155'

對于YEAR(2)顯示年份的範圍：'0' to '69'代表2000~2069年。

看如下示例：

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>year_test ( a </code><code>YEAR</code><code>(2));</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected, 1 warning (0.01 sec)</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>year_test </code><code>SELECT</code> <code>'1990'</code><code>;</code>

<code>mysql> </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>year_test;</code>

TIME類型占用3位元組。

顯示格式：'HH:MM:SS'

表達的時間範圍：'-838:59:59' to '838:59:59'

有人會奇怪為什麼TIME類型的時間可以大于23.因為TIME類型不僅可以用來儲存一天中的時間，也可以用來儲存時間間隔，同時這也解釋了為什麼TIME類型也可以存在負值。和DATETIME類型一樣，TIME類型同樣可以顯示微妙時間，但是在插入時，資料庫同樣會進行截斷操作。示例如下：

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>time_time ( a </code><code>TIME</code> <code>);</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.02 sec)</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>time_time </code><code>SELECT</code> <code>'14:30:56.3543534'</code><code>;</code>

<code>Query OK, 1 row affected, 1 warning (0.01 sec)</code>

<code>Records: 1 Duplicates: 0 Warnings: 1</code>

<code>mysql> </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>time_time;</code>

本文轉自 kuchuli 51CTO部落格，原文連結：http://blog.51cto.com/lgdvsehome/1243676，如需轉載請自行聯系原作者

MySQL 類型屬性及時間，日期類型

繼續閱讀

禁止ubuntu系統彈出報錯界面

MySQL的4種隔離級别？出現問題

Ubuntu Linux下Apache的配置檔案

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

samba伺服器的功能

登入plsql 報錯 the account is locked --使用者被鎖

【Linux】UDP廣播封包接收速率問題

SequoiaDB巨杉資料庫C++驅動概述

Linux裝置模型（中）之上層容器

PowerPC平台 Linux移植三