爱程序网

MySQL数据库的基本数据类型

来源: 阅读:


整数类型

数值型数据类型主要用来存储数字,包含的类型有:

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

下面通过一个例子来查看各种类型的所占有的数据空间,注意,我使用的版本是mysql5.6.28,可以通过下面的命令来查看:

clip_image001

创建表tmp1,其中字段x、y、z、m、n数据类型依次为TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,SQL语句如下:

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

执行成功之后,便用DESC查看表结构,结果如下:

DESC tmp1;

clip_image002

可以看到,TINGINT占1个字节,SMALLINT占2个字节,MEDIUMINT占3个字节,INT占4个字节,BIGINT占8个字节

浮点数类型与定点数类型

MySQL使用浮点数和定点数来表示小数。包含的类型有:

浮点类型:FLOAT、DOUBLE。

定点类型:DECIMAL。

测试案例:

创建表tmp2,其中字段x、y、z数据类型依次为FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1),向表中插入数据5.12、5.15和5.123,SQL语句如下:

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

向表中插入数据:

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

clip_image003

可以看到在插入数据时,MySQL给出了一个警告信息,使用SHOW WARNINGS;语句查看警告信息:

SHOW WARNINGS;

clip_image004

说‘z’这一列的数据被截断了,使用下面的SQL语句看看刚刚插入的数据:

SELECT * FROM tmp2;

clip_image005

日期与时间类型

MySQL中有多种表示日期的数据类型。它们是:

DATETIME、DATE、TIMESTAMP、TIME和YEAR。

YEAR类型测试案例:

创建数据表tmp3,定义数据类型为YEAR的字段y,向表中插入值2010,’2010’,’2166’,SQL语句如下:

首先创建表tmp3:

CREATE TABLE tmp3( y YEAR );

向表中插入数据:

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

clip_image006

可以知道,YEAR类型的数据在MySQL中是按照字符串格式存储的。查看一下YEAR类型数据所占用的字节空间:

clip_image007

可以看到,MySQL中YEAR类型的数据占用一个字节的数据空间。

再次向表中插入数据:

INSERT INTO tmp3 values ('2166');

语句执行之后,MySQL给出了一条警告提示,使用SHOW查看错误信息:

SHOW WARNINGS;

clip_image008

可以看到,插入的第3个值2166超过了YEAR类型的取值范围,此时不能正常的执行插入操作,查看结果:

SELECT * FROM tmp3;

clip_image009

由结果可以看到,当插入值为数值类型的2010或者字符串类型的’2010’时,都正确的储存到了数据库中;而当插入值’2166’时,由于超出了YEAR类型的取值范围,因此,不能插入值。查看资料得知,MySQL中YEAR类型的数据的取值范围是1901~2155.这里的YEAR是指的YEAR(4),此外,还有YEAR(2)。对于YEAR(2),其显示年份的范围为1970~2070。在YEAR(2)的设置下,00~69代表2000~2069年

向tmp3表中y字段插入2位字符串表示的YEAR值,分别为’0’、’00’、’77’和’10’,SQL语句如下:

首先删除表中的数据:

DELETE FROM tmp3;

向表中插入数据:

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

查看结果:

SELECT * FROM tmp3;

clip_image010

由结果可以看到,字符串’0’和’00’的作用相同,分别都转换成了2000年;’77’转换为1977;’10’转换为2010。

向tmp3表中y字段插入2位数字表示表示的YEAR值,分别为0、78和11,SQL语句如下:

首先删除表中的数据:

DELETE FROM tmp3;

向表中插入数据:

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

查看结果:

SELECT * FROM tmp3;

clip_image011

由结果可以看到,0被转换为0000;78被转换为1978;11被转换为2011。

对比两种测试数据,可以发现:对于YEAR数据类型的数据,在进行一些特殊值插入时,以数字形式和以字符串形式进行插入时得到的结果是不一样的。

TIME类型测试案例:

创建数据表tmp4,定义数据类型为TIME的字段t,向表中插入值’10:05:05’,’23:23’,’2 10:10’,’3 02’,’10’,SQL语句如下:

首先创建表tmp4,

CREATE TABLE tmp4( t TIME );

向表中插入数据:

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

查看结果:

SELECT * FROM tmp4;

clip_image012

由结果可以看到,’10:05:05’被转换为10:05:05;’23:23’被转换为23:23:00;’2 10:10’被转换为58:10:00,’3 02’被转换为74:00:00;’10’被转换成00:00:10。

在使用’D HH’格式时,小时一定要使用双位数值,如果是小于10的小时数,应在前面加0。TIME类型占用3字节,显示的范围为“-838:59:59”~“838:59:59”。有人会奇怪为什么TIME类型的时间可以大于23。因为TIME类型不仅可以用来保存一天中的时间,也可以用来保存时间间隔,同时这也解释了为什么TIME类型也可以存在负值。和DATETIME类型一样,TIME类型同样可以显示微秒时间,但是在插入时,数据库同样会进行截断操作

向表tmp4中插入值’101112’,111213,’0’,107010,SQL语句如下:

首先删除表中的数据:

DELETE FROM tmp4;

向表中插入数据:

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

再向表中插入数据:

INSERT INTO tmp4 values ( 107010);

clip_image013

可以看到,在插入数据时,MySQL给出了一个警告提示信息,使用SHOW WARNINGS;查看警告信息,如下所示:

show warnings;

clip_image014

可以看到,第二次在插入记录的时候,数据超出了范围,原因是107010的分钟部分超过了60,分钟部分是不会超过60的,查看结果:

SELECT * FROM tmp4;

clip_image015

由结果可以看到,’101112’被转换为10:11:12;111213被转换为11:12:13;’0’被转换为00:00:00;107010因为是不合法的值,因此插入00:00:00。

也可以使用系统日期函数向TIME字段列插入值。

向tmp4表中插入系统当前时间,SQL语句如下:

首先删除表中的数据:

DELETE FROM tmp4;

向表中插入数据:

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

查看结果:

SELECT * FROM tmp4;

clip_image016

由结果可以看到,获取系统当前的日期时间插入到TIME类型列,都是系统当前的日期时间值。

DATE类型数据测试案例:

创建数据表tmp5,定义数据类型为DATE的字段d,向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式日期,SQL语句如下:

首先创建表tmp5:

CREATE TABLE tmp5(d DATE);

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

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

查看插入结果:

SELECT * FROM tmp5;

clip_image017

可以看到,各个不同类型的日期值都正确的插入到了数据表中。DATE占用3字节,可显示的日期范围为“1000-01-01”到“9999-12-31”。

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

首先删除表中的数据:

DELETE FROM tmp5;

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

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

查看插入结果:

SELECT * FROM tmp5;

clip_image018

对于插入的最后一个值,为什么不是1911-11-11或者2111-11-11之类的值呢?可以看到数据库将其视为离现在最近的一个年份,这可能不是一个非常好的习惯。如果没有特别的条件和要求,还是在输入时按照标准的“YYYY-MM-DD HH:MM:SS”格式来进行。

向tmp5表中插入YY-MM-DD和YYMMDD数字格式日期,SQL语句如下:

首先删除表中的数据:

DELETE FROM tmp5;

向表中插入YY-MM-DD和YYMMDD数字格式日期:

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

查看插入结果:

SELECT * FROM tmp5;

clip_image019

可以看到,插入的第一条数据有一个警告,但是插入成功,插入的值变为了0000-00-00

向tmp5表中插入系统当前日期,SQL语句如下:

首先删除表中的数据:

DELETE FROM tmp5;

向表中插入系统当前日期:

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

查看插入结果:

SELECT * FROM tmp5;

clip_image020

CURRENT_DATE只返回当前日期值,不包括时间部分;NOW()函数返回日期和时间值,在保存到数据库时,只保留了其日期部分。

MySQL允许“不严格”语法:任何标点符号都可以用作日期部分之间的间隔符。例如,’98-11-31’、’98.11.31’、’98/11/31’和’98@11@31’是等价的,这些值也可以正确的插入到数据库。

DATETIME数据类型测试案例:

创建数据表tmp6,定义数据类型为DATETIME的字段dt,向表中插入"YYYY-MM-DD HH:MM:SS”和"YYYYMMDDHHMMSS”字符串格式日期和时间值,SQL语句如下:

首先创建表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');

查看插入结果:

SELECT * FROM tmp6;

clip_image021

可以看到,各个不同类型的日期值都正确的插入到了数据表中。DATETIME占用8字节,是占用空间最多的一种日期类型。它既显示了日期,同时也显示了时间。其可以表达的日期范围为“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。

在MySQL 5.5版本之前(包括5.5版本),数据库的日期类型不能精确到微秒级别,任何的微秒数值都会被数据库截断。

不过MySQL数据库提供了函数MICROSECOND来提取日期中的微秒值

clip_image022

向tmp6表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”字符串格式日期和时间值,SQL语句如下:

首先删除表中的数据:

DELETE FROM tmp6;

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

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

查看插入结果:

SELECT * FROM tmp6;

clip_image023

向tmp6表中插入YYYYMMDDHHMMSS 和YYMMDDHHMMSS数字格式日期和时间值,SQL语句如下:

首先删除表中的数据:

DELETE FROM tmp6;

向表中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS数字格式日期和时间:

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

查看插入结果:

SELECT * FROM tmp6;

clip_image024

向tmp6表中插入系统当前日期和时间值,SQL语句如下:

首先删除表中的数据:

DELETE FROM tmp6;

向表中插入系统当前日期:

INSERT INTO tmp6 values( NOW() );

查看插入结果:

SELECT * FROM tmp6;

clip_image025

TIMESTAMP数据类型测试案例:

创建数据表tmp7,定义数据类型为TIMESTAMP的字段ts,向表中插入值’19950101010101’,’950505050505’,’1996-02-02 02:02:02’,’97@03@03 03@03@03’,121212121212,NOW(),SQL语句如下:

CREATE TABLE tmp7( ts TIMESTAMP);

向表中插入数据:

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

查看插入结果:

SELECT * FROM tmp7;

clip_image026

由结果可以看到,’19950101010101’被转换为1995-01-01 01:01:01;’950505050505’被转换为1995-05-05 05:05:05;’1996-02-02 02:02:02’被转换为1996-02-02 02:02:02;’97@03@03 03@03@03’被转换为1997-03-03 03:03:03;121212121212被转换为2012-12-12 12:12:12;NOW()被转换为系统当前日期时间2013-03-24 09:17:49。

TIMESTAMP与DATETIME除了存储字节和支持的范围不同外,还有一个最大的区别就是:DATETIME在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;而TIMESTAMP值的存储是以UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。

向tmp7表中插入当前日期,查看插入值,更改时区为东10区,再次查看插入值,SQL语句如下:

首先删除表中的数据:

DELETE FROM tmp7;

向表中插入系统当前日期:

INSERT INTO tmp7 values( NOW() );

查看当前时区下日期值:

clip_image027

查询结果为插入时的日期值,读者所在时区一般为东8区,下面修改当前时区为东10区,SQL语句如下:

set time_zone='+10:00';

再次查看插入时的日期值:

SELECT * FROM tmp7;

clip_image028

然而从MySQL 5.6.4版本开始,MySQL增加了对秒的小数部分(fractional second)的支持,具体语法为:

type_name(fsp)

其中,type_name的类型可以是TIME、DATETIME和TIMESTAMP。fsp表示支持秒的小数部分的精度,最大为6,表示微秒(microseconds);默认为0,表示没有小数部分,同时也是为了兼容之前版本中的TIME、DATETIME和TIMESTAMP类型。对于时间函数,如CURTIME()、SYSDATE()和UTC_TIMESTAMP()也增加了对fsp的支持,例如:

clip_image029

字符串类型

字符串类型用于存储字符串数据,除了可以存储字符串数据外,还可以存储声音和图片的二进制数据,但是一般情况下,声音和图片是不会存在数据库中的。

字符串有如下几种类型:

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

将不同字符串保存到CHAR(4)和VARCHAR(4)列,说明CHAR和VARCHAR之间的差别,如表5.6所示:

image

对比结果可以看到,CHAR(4) 定义了固定长度为4的列,不管存入的数据长度为多少,所占用的空间均为4个字节。VARCHAR(4) 定义的列所占的字节数为实际长度加1。

当查询时CHAR(4) 和VARCHAR(4) 的值并不一定相同

CHAR和VARCHAR类型测试案例:

创建tmp8表,定义字段ch和vch数据类型依次为CHAR(4)、VARCHAR(4)向表中插入数据“ab ”,SQL语句如下:

创建表tmp8:

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

输入数据:

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

查询结果:

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

clip_image030

从查询结果可以看到,ch在保存“ab ”时将末尾的两个空格删除了,而vch字段保留了末尾的两个空格。

ENUM类型测试案例:

创建表tmp9,定义ENUM类型的列enm('first','second','third'),查看列成员的索引值,SQL语句如下:

首先,创建tmp9表:

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

插入各个列值:

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

查看索引值:

SELECT enm, enm+0 FROM tmp9;

clip_image031

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

ENUM列总有一个默认值。如果将ENUM列声明为NULL,NULL值则为该列的一个有效值,并且默认值为NULL。如果ENUM列被声明为NOT NULL,其默认值为允许的值列表的第1个元素。

创建表tmp10,定义INT类型的soc字段,ENUM类型的字段level,列表值为('excellent','good', 'bad'),向表tmp10中插入数据’good’,1,2,3,’best’,SQL语句如下:

首先,创建数据表:

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

插入数据:

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

再次插入数据:

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

clip_image032

这里系统提示警告信息,但是插入成功。查询结果如下:

SELECT * FROM tmp10;

clip_image033

由结果可以看到,因为ENUM列表中的值在MySQL中都是以编号序列存储的,因此,插入列表中的值“good”或者插入其对应序号’2’的结果是相同的;

SET类型测试案例:

创建表tmp11,定义SET类型的字段s,取值列表为('a', 'b', 'c', 'd'),插入数据('a'),('a,b,a'),('c,a,d'),('a,x,b,y'),SQL语句如下:

首先创建表tmp11:

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

插入数据:

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

clip_image034

再次插入数据:

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

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

clip_image035

查看结果:

SELECT * FROM tmp11;

clip_image036

从结果可以看到,对于SET来说如果插入的值为重复的,则只取一个,例如“a,b,a”,则结果为“a,b”;如果插入了不按顺序排列值,则自动按顺序插入,例如“c,a,d”,结果为“a,c,d”;如果插入了不完全值,该值将被选取插入,例如插入值“a,b”;如果插入了错误值,该值被替换成NULL

clip_image037

二进制类型

MySQL中存储二进制数据的数据类型有:

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

BIT类型测试案例:

创建表tmp12,定义BIT(4)类型的字段b,向表中插入数据2、9、15、16。

首先创建表tmp12,SQL语句如下:

CREATE TABLE tmp12( b BIT(4) );

插入数据:

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

查询插入结果:

SELECT BIN(b+0) FROM tmp12;

clip_image038

b+0表示将二进制的结果转换为对应的数字的值,BIN() 函数将数字转换为二进制。从结果可以看到,成功的将3个数插入到表中。

BINARY类型测试案例:

创建表tmp13,定义BINARY(3)类型的字段b和VARBINARY(3)类型的字段vb,并向表中插入数据’5’,比较两个字段的存储空间。

首先创建表tmp13,输入SQL语句如下:

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

插入数据:

INSERT INTO tmp13 VALUES(5,5);

查看两个字段存储数据的长度:

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

clip_image039

可以看到,b字段的值数据长度为3,而vb字段的数据长度仅为插入的一个字符的长度1。

如果想要进一步确认’5’在两个字段中不同的存储方式,输入如下语句:

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

clip_image040

由执行结果可以看出,b字段和vb字段的长度是截然不同的,因为b字段不足的空间填充了’’,而vb字段则没有填充。

参考:

相关文章列表:
关于爱程序网 - 联系我们 - 广告服务 - 友情链接 - 网站地图 - 版权声明 - 人才招聘 - 帮助