手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL数据库的基本数据类型

[日期:2016-03-30] 来源:Linux社区  作者:doctorJoe [字体: ]

整数类型

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

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

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

linuxidc_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;

linuxidc_image002

可以看到,TINGINT占4位(1个字节),SMALLINT占6位,MEDIUMINT占9位,INT占11位,BIGINT占20位

浮点数类型与定点数类型

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);

linuxidc_image003

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

SHOW WARNINGS;

linuxidc_image004

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

SELECT * FROM tmp2;

linuxidc_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');

linuxidc_image006

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

linuxidc_image007

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

再次向表中插入数据:

INSERT INTO tmp3 values ('2166');

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

SHOW WARNINGS;

linuxidc_image008

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

SELECT * FROM tmp3;

linuxidc_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;

linuxidc_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;

linuxidc_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;

linuxidc_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);

linuxidc_image013

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

show warnings;

linuxidc_image014

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

SELECT * FROM tmp4;

linuxidc_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;

linuxidc_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;

linuxidc_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;

linuxidc_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;

linuxidc_image019

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

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

首先删除表中的数据:

DELETE FROM tmp5;

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

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

查看插入结果:

SELECT * FROM tmp5;

linuxidc_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;

linuxidc_image021

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

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

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

linuxidc_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;

linuxidc_image023

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

首先删除表中的数据:

DELETE FROM tmp6;

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

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

查看插入结果:

SELECT * FROM tmp6;

linuxidc_image024

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

首先删除表中的数据:

DELETE FROM tmp6;

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

INSERT INTO tmp6 values( NOW() );

查看插入结果:

SELECT * FROM tmp6;

linuxidc_image025

更多详情见请继续阅读下一页的精彩内容http://www.linuxidc.com/Linux/2016-03/129673p2.htm

linux
相关资讯       MySQL数据类型 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款