整數(shù)類型

數(shù)值型數(shù)據(jù)類型主要用來存儲數(shù)字，包含的類型有：

TINYINT、SMALLINT、MEDIUMINT、 INT(INTEGER）、 BIGINT。

下面通過一個例子來查看各種類型的所占有的數(shù)據(jù)空間，注意，我使用的版本是mysql5.6.28，可以通過下面的命令來查看：

創(chuàng)建表tmp1，其中字段x、y、z、m、n數(shù)據(jù)類型依次為TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，SQL語句如下：

CREATE TABLE tmp1 ( x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );

執(zhí)行成功之后，便用DESC查看表結(jié)構(gòu)，結(jié)果如下：

DESC tmp1;

可以看到，TINGINT占1個字節(jié)，SMALLINT占2個字節(jié)，MEDIUMINT占3個字節(jié)，INT占4個字節(jié)，BIGINT占8個字節(jié)

浮點數(shù)類型與定點數(shù)類型

MySQL使用浮點數(shù)和定點數(shù)來表示小數(shù)。包含的類型有：

浮點類型：FLOAT、DOUBLE。

定點類型：DECIMAL。

測試案例：

創(chuàng)建表tmp2，其中字段x、y、z數(shù)據(jù)類型依次為FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1)，向表中插入數(shù)據(jù)5.12、5.15和5.123，SQL語句如下：

CREATE TABLE tmp2 ( x FLOAT(5,1), y DOUBLE(5,1), z DECIMAL(5,1) );

向表中插入數(shù)據(jù)：

INSERT INTO tmp2 VALUES(5.12, 5.15, 5.123);

可以看到在插入數(shù)據(jù)時，MySQL給出了一個警告信息，使用SHOW WARNINGS;語句查看警告信息：

SHOW WARNINGS;

說‘z’這一列的數(shù)據(jù)被截斷了，使用下面的SQL語句看看剛剛插入的數(shù)據(jù):

SELECT * FROM tmp2;

日期與時間類型

MySQL中有多種表示日期的數(shù)據(jù)類型。它們是:

DATETIME、DATE、TIMESTAMP、TIME和YEAR。

YEAR類型測試案例：

創(chuàng)建數(shù)據(jù)表tmp3，定義數(shù)據(jù)類型為YEAR的字段y，向表中插入值2010，’2010’，’2166’，SQL語句如下：

首先創(chuàng)建表tmp3：

CREATE TABLE tmp3( y YEAR );

向表中插入數(shù)據(jù)：

INSERT INTO tmp3 values(2010),('2010');

可以知道，YEAR類型的數(shù)據(jù)在MySQL中是按照字符串格式存儲的。查看一下YEAR類型數(shù)據(jù)所占用的字節(jié)空間:

可以看到，MySQL中YEAR類型的數(shù)據(jù)占用一個字節(jié)的數(shù)據(jù)空間。

再次向表中插入數(shù)據(jù)：

INSERT INTO tmp3 values ('2166');

語句執(zhí)行之后，MySQL給出了一條警告提示，使用SHOW查看錯誤信息：

SHOW WARNINGS;

可以看到，插入的第3個值2166超過了YEAR類型的取值范圍，此時不能正常的執(zhí)行插入操作，查看結(jié)果：

SELECT * FROM tmp3;

由結(jié)果可以看到，當插入值為數(shù)值類型的2010或者字符串類型的’2010’時，都正確的儲存到了數(shù)據(jù)庫中；而當插入值’2166’時，由于超出了YEAR類型的取值范圍，因此，不能插入值。查看資料得知，MySQL中YEAR類型的數(shù)據(jù)的取值范圍是1901~2155.這里的YEAR是指的YEAR(4),此外，還有YEAR(2)。對于YEAR(2)，其顯示年份的范圍為1970～2070。在YEAR(2)的設(shè)置下，00～69代表2000～2069年。

向tmp3表中y字段插入2位字符串表示的YEAR值，分別為’0’、’00’、’77’和’10’，SQL語句如下：

首先刪除表中的數(shù)據(jù)：

DELETE FROM tmp3;

向表中插入數(shù)據(jù)：

INSERT INTO tmp3 values('0'),('00'),('77'),('10');

查看結(jié)果：

SELECT * FROM tmp3;

由結(jié)果可以看到，字符串’0’和’00’的作用相同，分別都轉(zhuǎn)換成了2000年；’77’轉(zhuǎn)換為1977；’10’轉(zhuǎn)換為2010。

向tmp3表中y字段插入2位數(shù)字表示表示的YEAR值，分別為0、78和11，SQL語句如下：

首先刪除表中的數(shù)據(jù)：

DELETE FROM tmp3;

向表中插入數(shù)據(jù)：

INSERT INTO tmp3 values(0),(78),(11);

查看結(jié)果：

SELECT * FROM tmp3;

由結(jié)果可以看到，0被轉(zhuǎn)換為0000；78被轉(zhuǎn)換為1978；11被轉(zhuǎn)換為2011。

對比兩種測試數(shù)據(jù)，可以發(fā)現(xiàn)：對于YEAR數(shù)據(jù)類型的數(shù)據(jù)，在進行一些特殊值插入時，以數(shù)字形式和以字符串形式進行插入時得到的結(jié)果是不一樣的。

TIME類型測試案例：

創(chuàng)建數(shù)據(jù)表tmp4，定義數(shù)據(jù)類型為TIME的字段t，向表中插入值’10:05:05’，’23:23’，’2 10:10’，’3 02’，’10’，SQL語句如下：

首先創(chuàng)建表tmp4，

CREATE TABLE tmp4( t TIME );

向表中插入數(shù)據(jù)：

INSERT INTO tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'),('10');

查看結(jié)果：

SELECT * FROM tmp4;

由結(jié)果可以看到，’10:05:05’被轉(zhuǎn)換為10:05:05；’23:23’被轉(zhuǎn)換為23:23:00；’2 10:10’被轉(zhuǎn)換為58:10:00，’3 02’被轉(zhuǎn)換為74:00:00；’10’被轉(zhuǎn)換成00:00:10。

在使用’D HH’格式時，小時一定要使用雙位數(shù)值，如果是小于10的小時數(shù)，應(yīng)在前面加0。TIME類型占用3字節(jié)，顯示的范圍為“-838：59：59”～“838：59：59”。有人會奇怪為什么TIME類型的時間可以大于23。因為TIME類型不僅可以用來保存一天中的時間，也可以用來保存時間間隔，同時這也解釋了為什么TIME類型也可以存在負值。和DATETIME類型一樣，TIME類型同樣可以顯示微秒時間，但是在插入時，數(shù)據(jù)庫同樣會進行截斷操作

向表tmp4中插入值’101112’，111213,’0’，107010，SQL語句如下：

首先刪除表中的數(shù)據(jù)：

DELETE FROM tmp4;

向表中插入數(shù)據(jù)：

INSERT INTO tmp4 values('101112'),(111213),( '0');

再向表中插入數(shù)據(jù)：

INSERT INTO tmp4 values ( 107010);

可以看到，在插入數(shù)據(jù)時，MySQL給出了一個警告提示信息，使用SHOW WARNINGS;查看警告信息，如下所示：

show warnings;

可以看到，第二次在插入記錄的時候，數(shù)據(jù)超出了范圍，原因是107010的分鐘部分超過了60，分鐘部分是不會超過60的，查看結(jié)果：

SELECT * FROM tmp4;

由結(jié)果可以看到，’101112’被轉(zhuǎn)換為10:11:12；111213被轉(zhuǎn)換為11:12:13；’0’被轉(zhuǎn)換為00:00:00；107010因為是不合法的值，因此插入00:00:00。

也可以使用系統(tǒng)日期函數(shù)向TIME字段列插入值。

向tmp4表中插入系統(tǒng)當前時間，SQL語句如下：

首先刪除表中的數(shù)據(jù)：

DELETE FROM tmp4;

向表中插入數(shù)據(jù)：

INSERT INTO tmp4 values (CURRENT_TIME) ,(NOW());

查看結(jié)果：

SELECT * FROM tmp4;

由結(jié)果可以看到，獲取系統(tǒng)當前的日期時間插入到TIME類型列，都是系統(tǒng)當前的日期時間值。

DATE類型數(shù)據(jù)測試案例：

創(chuàng)建數(shù)據(jù)表tmp5，定義數(shù)據(jù)類型為DATE的字段d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式日期，SQL語句如下：

首先創(chuàng)建表tmp5：

CREATE TABLE tmp5(d DATE);

向表中插入“YYYY-MM-DD”和“YYYYMMDD”格式日期：

INSERT INTO tmp5 values('1998-08-08'),('19980808'),('20101010');

查看插入結(jié)果：

SELECT * FROM tmp5;

可以看到，各個不同類型的日期值都正確的插入到了數(shù)據(jù)表中。DATE占用3字節(jié)，可顯示的日期范圍為“1000-01-01”到“9999-12-31”。

向tmp5表中插入“YY-MM-DD”和“YYMMDD”字符串格式日期，SQL語句如下：

首先刪除表中的數(shù)據(jù)：

DELETE FROM tmp5;

向表中插入“YY-MM-DD”和“YYMMDD”格式日期：

INSERT INTO tmp5 values ('99-09-09'),( '990909'), ('000101') ,('111111');

查看插入結(jié)果：

SELECT * FROM tmp5;

對于插入的最后一個值，為什么不是1911-11-11或者2111-11-11之類的值呢？可以看到數(shù)據(jù)庫將其視為離現(xiàn)在最近的一個年份，這可能不是一個非常好的習慣。如果沒有特別的條件和要求，還是在輸入時按照標準的“YYYY-MM-DD HH：MM：SS”格式來進行。

向tmp5表中插入YY-MM-DD和YYMMDD數(shù)字格式日期，SQL語句如下：

首先刪除表中的數(shù)據(jù)：

DELETE FROM tmp5;

向表中插入YY-MM-DD和YYMMDD數(shù)字格式日期：

INSERT INTO tmp5 values (99-09-09),(990909), ( 000101) ,( 111111);

查看插入結(jié)果：

SELECT * FROM tmp5;

可以看到，插入的第一條數(shù)據(jù)有一個警告，但是插入成功，插入的值變?yōu)榱?a dir='ltr' x-apple-data-detectors='true' x-apple-data-detectors-type='telephone' x-apple-data-detectors-result='23'>0000-00-00

向tmp5表中插入系統(tǒng)當前日期，SQL語句如下：

首先刪除表中的數(shù)據(jù)：

DELETE FROM tmp5;

向表中插入系統(tǒng)當前日期：

INSERT INTO tmp5 values( CURRENT_DATE() ),( NOW() );

查看插入結(jié)果：

SELECT * FROM tmp5;

CURRENT_DATE只返回當前日期值，不包括時間部分；NOW()函數(shù)返回日期和時間值，在保存到數(shù)據(jù)庫時，只保留了其日期部分。

MySQL允許“不嚴格”語法：任何標點符號都可以用作日期部分之間的間隔符。例如，’98-11-31’、’98.11.31’、’98/11/31’和

首先創(chuàng)建表tmp6：

CREATE TABLE tmp6( dt DATETIME );

向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”格式日期：

INSERT INTO tmp6 values('1998-08-08 08:08:08'),('19980808080808'),('20101010101010');

查看插入結(jié)果：

SELECT * FROM tmp6;

可以看到，各個不同類型的日期值都正確的插入到了數(shù)據(jù)表中。DATETIME占用8字節(jié)，是占用空間最多的一種日期類型。它既顯示了日期，同時也顯示了時間。其可以表達的日期范圍為“1000-01-01 00：00：00”到“9999-12-31 23：59：59”。

在MySQL 5.5版本之前(包括5.5版本)，數(shù)據(jù)庫的日期類型不能精確到微秒級別，任何的微秒數(shù)值都會被數(shù)據(jù)庫截斷。

不過MySQL數(shù)據(jù)庫提供了函數(shù)MICROSECOND來提取日期中的微秒值

向tmp6表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”字符串格式日期和時間值，SQL語句如下：

首先刪除表中的數(shù)據(jù)：

DELETE FROM tmp6;

向表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”格式日期：

INSERT INTO tmp6 values('99-09-09 09:09:09'),('990909090909'),('101010101010');

查看插入結(jié)果：

SELECT * FROM tmp6;

向tmp6表中插入YYYYMMDDHHMMSS 和YYMMDDHHMMSS數(shù)字格式日期和時間值，SQL語句如下：

首先刪除表中的數(shù)據(jù)：

DELETE FROM tmp6;

向表中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS數(shù)字格式日期和時間：

INSERT INTO tmp6 values(19990909090909), (101010101010);

查看插入結(jié)果：

SELECT * FROM tmp6;

向tmp6表中插入系統(tǒng)當前日期和時間值，SQL語句如下：

首先刪除表中的數(shù)據(jù)：

DELETE FROM tmp6;

向表中插入系統(tǒng)當前日期：

INSERT INTO tmp6 values( NOW() );

查看插入結(jié)果：

SELECT * FROM tmp6;

TIMESTAMP數(shù)據(jù)類型測試案例：

創(chuàng)建數(shù)據(jù)表tmp7，定義數(shù)據(jù)類型為TIMESTAMP的字段ts，向表中插入值’19950101010101’，’950505050505’，’1996-02-02 02:02:02’，’97@03@03 03@03@03’，121212121212，NOW()，SQL語句如下：

CREATE TABLE tmp7( ts TIMESTAMP);

向表中插入數(shù)據(jù)：

INSERT INTO tmp7 values ('19950101010101'),('950505050505'),('1996-02-02 02:02:02'),('97@03@03 03@03@03'),(121212121212),( NOW() );

查看插入結(jié)果：

SELECT * FROM tmp7;

由結(jié)果可以看到，’19950101010101’被轉(zhuǎn)換為1995-01-01 01:01:01；’950505050505’被轉(zhuǎn)換為1995-05-05 05:05:05；’1996-02-02 02:02:02’被轉(zhuǎn)換為1996-02-02 02:02:02；’97@03@03 03@03@03’被轉(zhuǎn)換為1997-03-03 03:03:03；121212121212被轉(zhuǎn)換為2012-12-12 12:12:12；NOW()被轉(zhuǎn)換為系統(tǒng)當前日期時間2013-03-24 09:17:49。

TIMESTAMP與DATETIME除了存儲字節(jié)和支持的范圍不同外，還有一個最大的區(qū)別就是：DATETIME在存儲日期數(shù)據(jù)時，按實際輸入的格式存儲，即輸入什么就存儲什么，與時區(qū)無關(guān)；而TIMESTAMP值的存儲是以UTC（世界標準時間）格式保存的，存儲時對當前時區(qū)進行轉(zhuǎn)換，檢索時再轉(zhuǎn)換回當前時區(qū)。即查詢時，根據(jù)當前時區(qū)的不同，顯示的時間值是不同的。

向tmp7表中插入當前日期，查看插入值，更改時區(qū)為東10區(qū)，再次查看插入值，SQL語句如下：

首先刪除表中的數(shù)據(jù)：

DELETE FROM tmp7;

向表中插入系統(tǒng)當前日期：

INSERT INTO tmp7 values( NOW() );

查看當前時區(qū)下日期值：

查詢結(jié)果為插入時的日期值，讀者所在時區(qū)一般為東8區(qū)，下面修改當前時區(qū)為東10區(qū)，SQL語句如下：

set time_zone=' 10:00';

再次查看插入時的日期值：

SELECT * FROM tmp7;

然而從MySQL 5.6.4版本開始，MySQL增加了對秒的小數(shù)部分(fractional second)的支持，具體語法為：

type_name（fsp）

其中，type_name的類型可以是TIME、DATETIME和TIMESTAMP。fsp表示支持秒的小數(shù)部分的精度，最大為6，表示微秒(microseconds);默認為0，表示沒有小數(shù)部分，同時也是為了兼容之前版本中的TIME、DATETIME和TIMESTAMP類型。對于時間函數(shù)，如CURTIME()、SYSDATE()和UTC_TIMESTAMP()也增加了對fsp的支持，例如：

字符串類型

字符串類型用于存儲字符串數(shù)據(jù)，除了可以存儲字符串數(shù)據(jù)外，還可以存儲聲音和圖片的二進制數(shù)據(jù)，但是一般情況下，聲音和圖片是不會存在數(shù)據(jù)庫中的。

字符串有如下幾種類型：

CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

將不同字符串保存到CHAR(4)和VARCHAR(4)列，說明CHAR和VARCHAR之間的差別，如表5.6所示：

對比結(jié)果可以看到，CHAR(4) 定義了固定長度為4的列，不管存入的數(shù)據(jù)長度為多少，所占用的空間均為4個字節(jié)。VARCHAR(4) 定義的列所占的字節(jié)數(shù)為實際長度加1。

當查詢時CHAR(4) 和VARCHAR(4) 的值并不一定相同

CHAR和VARCHAR類型測試案例：

創(chuàng)建tmp8表，定義字段ch和vch數(shù)據(jù)類型依次為CHAR(4)、VARCHAR(4)向表中插入數(shù)據(jù)“ab ”，SQL語句如下：

創(chuàng)建表tmp8：

CREATE TABLE tmp8(ch CHAR(4), vch VARCHAR(4));

輸入數(shù)據(jù)：

INSERT INTO tmp8 VALUES('ab ', 'ab ');

查詢結(jié)果：

SELECT concat('(', ch, ')'), concat('(',vch,')') FROM tmp8;

從查詢結(jié)果可以看到，ch在保存“ab ”時將末尾的兩個空格刪除了，而vch字段保留了末尾的兩個空格。

ENUM類型測試案例：

創(chuàng)建表tmp9，定義ENUM類型的列enm('first'，'second'，'third')，查看列成員的索引值，SQL語句如下：

首先，創(chuàng)建tmp9表：

CREATE TABLE tmp9( enm ENUM('first','second','third') );

插入各個列值：

INSERT INTO tmp9 values('first'),('second') ,('third') , (NULL);

查看索引值：

SELECT enm, enm 0 FROM tmp9;

可以看到，這里的索引值和前面所述的相同。

ENUM列總有一個默認值。如果將ENUM列聲明為NULL，NULL值則為該列的一個有效值，并且默認值為NULL。如果ENUM列被聲明為NOT NULL，其默認值為允許的值列表的第1個元素。

創(chuàng)建表tmp10，定義INT類型的soc字段，ENUM類型的字段level，列表值為('excellent','good', 'bad')，向表tmp10中插入數(shù)據(jù)’good’，1，2，3，’best’，SQL語句如下：

首先，創(chuàng)建數(shù)據(jù)表：

CREATE TABLE tmp10 (soc INT, level enum('excellent', 'good','bad') );

插入數(shù)據(jù)：

INSERT INTO tmp10 values(70,'good'), (90,1),(75,2),(50,3);

再次插入數(shù)據(jù)：

INSERT INTO tmp10 values (100,'best');

這里系統(tǒng)提示警告信息，但是插入成功。查詢結(jié)果如下：

SELECT * FROM tmp10;

由結(jié)果可以看到，因為ENUM列表中的值在MySQL中都是以編號序列存儲的，因此，插入列表中的值“good”或者插入其對應(yīng)序號’2’的結(jié)果是相同的；

SET類型測試案例：

創(chuàng)建表tmp11，定義SET類型的字段s，取值列表為('a', 'b', 'c', 'd')，插入數(shù)據(jù)('a')，('a,b,a')，('c,a,d')，('a,x,b,y')，SQL語句如下：

首先創(chuàng)建表tmp11：

CREATE TABLE tmp11 ( s SET('a', 'b', 'c', 'd'));

插入數(shù)據(jù)：

INSERT INTO tmp11 values('a'),( 'a,b,a'),('c,a,d');

再次插入數(shù)據(jù)：

INSERT INTO tmp11 values ('a,x,b,y');

由于插入了SET列不支持的值，因此MySQL給出警告提示。

查看結(jié)果：

SELECT * FROM tmp11;

從結(jié)果可以看到，對于SET來說如果插入的值為重復(fù)的，則只取一個，例如“a,b,a”，則結(jié)果為“a,b”；如果插入了不按順序排列值，則自動按順序插入，例如“c,a,d”，結(jié)果為“a,c,d”；如果插入了不完全值，該值將被選取插入，例如插入值“a,b”；如果插入了錯誤值，該值被替換成NULL

二進制類型

MySQL中存儲二進制數(shù)據(jù)的數(shù)據(jù)類型有：

BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

BIT類型測試案例：

創(chuàng)建表tmp12，定義BIT(4)類型的字段b，向表中插入數(shù)據(jù)2、9、15、16。

首先創(chuàng)建表tmp12，SQL語句如下：

CREATE TABLE tmp12( b BIT(4) );

插入數(shù)據(jù)：

INSERT INTO tmp12 VALUES(2), (9), (15);

查詢插入結(jié)果：

SELECT BIN(b 0) FROM tmp12;

b 0表示將二進制的結(jié)果轉(zhuǎn)換為對應(yīng)的數(shù)字的值，BIN() 函數(shù)將數(shù)字轉(zhuǎn)換為二進制。從結(jié)果可以看到，成功的將3個數(shù)插入到表中。

BINARY類型測試案例：

創(chuàng)建表tmp13，定義BINARY(3)類型的字段b和VARBINARY(3)類型的字段vb，并向表中插入數(shù)據(jù)’5’，比較兩個字段的存儲空間。

首先創(chuàng)建表tmp13，輸入SQL語句如下：

CREATE TABLE tmp13(b binary(3), vb varbinary(30));

插入數(shù)據(jù)：

INSERT INTO tmp13 VALUES(5,5);

查看兩個字段存儲數(shù)據(jù)的長度：

SELECT length(b), length(vb) FROM tmp13;

可以看到，b字段的值數(shù)據(jù)長度為3，而vb字段的數(shù)據(jù)長度僅為插入的一個字符的長度1。

如果想要進一步確認’5’在兩個字段中不同的存儲方式，輸入如下語句：

SELECT b,vb,b = '5', b='5\0\0',vb='5',vb = '5\0\0' FROM tmp13;

由執(zhí)行結(jié)果可以看出，b字段和vb字段的長度是截然不同的，因為b字段不足的空間填充了’\0’，而vb字段則沒有填充。

參考：

MySQL日期數(shù)據(jù)類型、時間類型使用總結(jié)
MySQL技術(shù)內(nèi)幕：時間和日期數(shù)據(jù)類型

本站僅提供存儲服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點擊舉報。

国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

整數(shù)類型

浮點數(shù)類型與定點數(shù)類型

測試案例：

日期與時間類型

YEAR類型測試案例：

TIME類型測試案例：

DATE類型數(shù)據(jù)測試案例：

TIMESTAMP數(shù)據(jù)類型測試案例：

字符串類型

CHAR和VARCHAR類型測試案例：

ENUM類型測試案例：

SET類型測試案例：

二進制類型

BIT類型測試案例：

BINARY類型測試案例：

參考：