MySQL/Language/Data Types

維基教科書,自由的教學讀本
跳至導覽 跳至搜尋

VARCHAR/CHAR[編輯]

VARCHAR是CHARACTER VARYING的縮寫. 最大列長上限是65,535字符。實際長度是所存儲字符串長度,再加上1或2位元組(如果設定最大長度<255則為1位元組)用於存儲字符串長度。

CHAR(n)類似於varchar(n),但佔據固定存儲長度。而varchar需要空間來存儲實際的文本。

CHAR 類型用於定長字符串,並且必須在圓括號內用一個大小修飾符來定義。這個大小修飾符的範圍從 0-255。比指定長度大的值將被截短,而比指定長度小的值將會用空格作填補。

CHAR 類型與VARCHAR類型,可以使用 BINARY 修飾符。當用於比較運算時,這個修飾符使 CHAR 以二進位方式參於運算,而不是以傳統的區分大小寫的方式。  

TEXT 與 BLOB[編輯]

BLOB 或 TEXT 列的最大長度是65,535字符。真實長度是存儲數據加上2位元組(或1位元組當長度<255)。比指定類型支持的最大範圍大的值將被自動截短。BLOB / TEXT數據並不存儲在表的數據文件中。這使得各種操作(INSERT / UPDATE / DELETE / SELECT)涉及BLOB / TEXT數據更慢,但其他操作更快。

TEXT 和 BLOB 類型在分類和比較上存在區別。BLOB 類型區分大小寫,而 TEXT 不區分大小寫。

其他類型:

  • TINYBLOB/TINYTEXT: 0-255位元組
  • MEDIUMBLOB/MEDIUMTEXT: 0 - 16 777 215位元組
  • LOGNGBLOB/LONGTEXT:0-4 294 967 295位元組

整型[編輯]

MySQL 中支持的 5 個主要整數類型: TINYINT,SMALLINT,MEDIUMINT,INT 和 BIGINT。存儲長度分別是1、2、3、4、8位元組。

可選的顯示寬度指示器是對 SQL 標準進行擴展。例如,欄位的類型為 INT(6),則包含數字少於 6個數字的值從資料庫中檢索出來時能夠自動地用空格填充。需要注意的是,使用一個寬度指示器不會影響欄位的大小和它可以存儲的值的範圍。

欄位存儲一個超出許可範圍的數字,MySQL 會根據允許範圍最接近它的一端截短後再進行存儲。

MySQL 會在不合規定的值插入表前自動修改為 0。

UNSIGNED 修飾符規定欄位只保存正值。

ZEROFILL 修飾符規定 0(不是空格)可以用來補輸出的值。使用這個修飾符可以阻止 MySQL 資料庫存儲負值。

浮點[編輯]

FLOAT 數值類型用於表示單精度浮點數值,DOUBLE 數值類型用於表示雙精度浮點數值。

類型也帶有附加參數:一個顯示寬度指示器和一個小數點指示器。如語句 FLOAT(7,3) 規定顯示的值不會超過 7 位數字,小數點後面帶有 3 位數字。小數點後面的位數超過允許範圍的值,MySQL 會自動將它四捨五入為最接近它的值,再插入它。

decimal[編輯]

形式為decimal(n,m)

例如:decimal(4,2)意味著最大99.99 (而不是9999.99如你期望那樣)。

Dates[編輯]

在三種日期類型中,DATETIME, DATE, TIMESTAMP。

  • DATE類型僅有日期數據沒有時間數據;DATE值採取 'YYYY-MM-DD' 格式。3位元組長。範圍1000-01-01/9999-12-31。
  • DATETIME包含日期與時間數據。8位元組長。格式為 YYYY-MM-DD HH:MM:SS。範圍 1000-01-01 00:00:00/9999-12-31 23:59:59
  • TIMESTAMP也包含日期與時間數據,但僅覆蓋1970-2037。4位元組長。即1970-01-01 00:00:00 UTC至2038-01-19 03:14:07 UTC

TIME類型僅表示時間。格式'HH:MM:SS',或者時間間隔。例如: -02:00:00表示"過去2個小時"). 表示範圍為: '-838:59:59' => '838:59:59'. 3位元組長。

YEAR類型表示年份。1位元組長。範圍1901/2155


MySQL不會自動把時間補上今天的日期。


基於Unix的時間戳,開始於Unix Epoch,上限到(2038年).

mysql> SET time_zone = '+00:00'; -- GMT
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT FROM_UNIXTIME(-1);
+-------------------+
| FROM_UNIXTIME(-1) |
+-------------------+
| NULL              |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(0); -- "Epoch"
+---------------------+
| FROM_UNIXTIME(0)    |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2145916799);
+---------------------------+
| FROM_UNIXTIME(2145916799) |
+---------------------------+
| 2037-12-31 23:59:59       |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2145916800);
+---------------------------+
| FROM_UNIXTIME(2145916800) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

獲得當前日期+時間(date + time)函數:now():在執行開始時求值

+---------------------+
| now() |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+

sysdate() 日期時間函數跟 now() 類似,但sysdate() 在函數執行時動態得到值。

獲得當前時間戳函數:current_timestamp()

日期時間轉為字符串的函數date_format(date,format), time_format(time,format):

select date_format('2013-03-09','%Y-%c-%d');

字符串轉為日期時間的函數:

select STR_TO_DATE('2013-03-09','%Y-%c-%d');
%W 星期名字(Sunday……Saturday)  
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)  
%Y 年, 数字, 4 位  
%y 年, 数字, 2 位  
%a 缩写的星期名字(Sun……Sat)  
%d 月份中的天数, 数字(00……31)  
%e 月份中的天数, 数字(0……31)  
%m 月, 数字(01……12)  
%c 月, 数字(1……12)  
%b 缩写的月份名字(Jan……Dec)  
%j 一年中的天数(001……366)  
%H 小时(00……23)  
%k 小时(0……23)  
%h 小时(01……12)  
%I 小时(01……12)  
%l 小时(1……12)  
%i 分钟, 数字(00……59)  
%r 时间,12 小时(hh:mm:ss [AP]M)  
%T 时间,24 小时(hh:mm:ss)  
%S 秒(00……59)  
%s 秒(00……59)  
%p AM或PM  
%w 一个星期中的天数(0=Sunday ……6=Saturday )  
%U 星期(0……52), 这里星期天是星期的第一天  
%u 星期(0……52), 这里星期一是星期的第一天  
%% 一个文字“%”。

to_days(date)是從公元0年1月0日開始的天數:

select to_days('0000-00-01'); -- 1

from_days(days)是給出天數返回日期:

mysql> select from_days(1000);
+-----------------+
| from_days(1000) |
+-----------------+
| 0002-09-27      |
+-----------------+
1 row in set (0.00 sec)

時間、秒)轉換函數:time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'

拼湊日期、時間函數:makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'
select maketime(12,15,30); -- '12:15:30'

Unix 時間戳、日期)轉換函數

unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)

為日期增加一個時間間隔:date_add()、日期減去一個時間間隔date_sub():

select date_add(@dt, interval 1 quarter);

日期、時間相減函數:datediff(date1,date2), timediff(time1,time2)

時間戳(timestamp)轉換、增、減函數:

timestamp(date) -- date to timestamp
timestamp(dt,time) -- dt + time
timestampadd(unit,interval,datetime_expr) --
timestampdiff(unit,datetime_expr1,datetime_expr2) --

時區(timezone)轉換函數:

convert_tz(dt,from_tz,to_tz)

set 與 enum[編輯]

MySQL 還支持兩種複合數據類型 ENUM 和 SET,它們擴展了 SQL 規範。雖然這些類型在技術上是字符串類型,但是可以被視為不同的數據類型。

ENUM 類型因為只允許在集合中取得一個值,有點類似於單選項。表示相互排拆的數據,如人類的性別。ENUM 類型可使用 null 值。欄位中插入非預定義的值都會使 MySQL 插入一個空字符串。如果插入值的大小寫與集合中的值的大小寫不匹配,MySQL 會自動使用插入值的大小寫轉換成與集合中大小寫一致的值。因為ENUM 類型在系統內部可以存儲為數字,並且從 1 開始用數字做索引。一個 ENUM 類型最多可以包含 65536 個元素,其中一個元素被 MySQL 保留,用來存儲錯誤信息,這個錯誤值用索引 0 或者一個空字符串表示。

SET 類型從預定義的集合中取得任意數量的多個值。並且與 ENUM 類型相同的是任何試圖在 SET 類型欄位中插入非預定義的值都會使 MySQL 插入一個空字符串。如果插入一個即有合法的元素又有非法的元素的記錄,MySQL 將會保留合法的元素,除去非法的元素。一個 SET 類型最多可以包含 64 項元素。在 SET 元素中值被存儲為一個分離的「位」序列,這些「位」表示與它相對應的元素。所以 SET 類型中不可能包含兩個相同的元素。希望從 SET 類型欄位中找出非法的記錄只需查找包含空字符串或二進位值為 0 的行。


例如:

SET("madam", "mister")    -- 合法的:空集, "madam", "mister", "madam, mister", "mister, madam"

ENUM("madam", "mister")   -- 合法的:空集, "madam" or  "mister"

類型轉換與字符串拼接函數[編輯]

CAST(expr AS type)
convert(XX,TYPE)
CONCAT(str1,str2,...) # 可以是数值作为参数;如果有一个参数为NULL则结果为NULL
CONCAT_WS(separator,str1,str2,...) #concat with separator 带分隔符
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
LPAD(str,len,padstr),RPAD(str,len,padstr):字符串左/友填充函数,支持整数类型的填充

參考文獻[編輯]