mysql里的数据类型 mysql数据类型怎么用

SQL语法预览：

创建表字段数据类型：【create table 表名(字段名称 数据类型);】

插入字段值：【insert into 表名 values (值1,值2…值n);】

查看格式化值：【select * 或【格式1,格式2…格式n】 from 表名;】

详解：

一、 MySQL数据类型介绍

MySQL支持多种数据类型，主要有数值类型、日期类型、时间类型和字符串类型。

1. 整数类型

数据类型主要用来存储数字，MySQL提供多种可供选择的整数类型，用整数作为数据类型的字段可以设置auto_increment自增约束条件。

案例：创建数据库db_demo2，创建表tb_temp1，其中包含字段a、b、c、d、e的数据类型分别为tinyint、smallint、mediumint、int(Integer)、bigint，SQL语句如下：

create database db_demo2;

use db_demo2;

create table tb_temp1(a tinyint,b smallint,c mediumint,d int,e bigint);

desc temp1;

执行结果如下：

2. 浮点和定点数据类型

MySQL中使用浮点数和定点数来表示小数。浮点有分为单浮点和双浮点。

案例：创建数据表tb_temp2，其中包含字段x、y、z的数据类型依次为float(5,1)、double(5,1)和decimal(5,1)，向表中插入数据5.12、5.15和5.123，SQL语句：

create table tb_temp2(x float(5,1),y double(5,1),z decimal(5,1));

insert into tb_temp2 values(5.12,5.15,5.123);

执行结果如下：

3. 日期与时间类型

(1) YEAR

YEAR类型是一个单字节类型，用于表示年，在存储时只需要1个字节。可以使用各种格式指定YEAR值，如下所示：

·以4位字符串或者4位数字格式表示的YEAR，范围为1901~2155。输入格式'YYYY'或者YYYY。

·以2位字符串格式表示的YEAR，范围为00到99。00~69转换为2000~2069；70~99转换为1970~1999；

·以2位数字表示的YEAR值，范围为1~99。1~69值是2001~2069；70~99范围值转换为1970~1999，而在这里0值被转换为0000；

案例：创建数据表tb_temp3，定义字段类型为YEAR的字段y，向表中插入数值2017，'2017'，'0'，'00'，'77'，'10' ，'78'和'11'，SQL语句：

create table tb_temp3(y YEAR);

insert into tb_temp3 values(2017),('2017'),('0'),('00',('77'),('10'),('78'),('11');

执行结果如下：

(2) TIME

TIME类型用于表示时间信息，在存储时需要3个字节。表示某一事件过去的时间和两个事件之间的时间间隔。可以大于24甚至为负值。 ·

·'D HH:MM:SS’格式的字符串。有'HH:MM'，'D HH:MM' ,'D HH' ,'SS'。D表示日，可以取0~34之间的值。在插入数据库时D被转换为小时保存(D*24+HH)。

·'HHMMSS'格式的数值，会自动分成HH:MM:SS但各自有取值范围，超过则输入不合法。

案例：创建数据表tb_temp4，定义字段类型为TIME的字段t，向表中插入数值'10:05:05' ，'23:23'，'2 10:10'，'3 02' ，'10' ，'101112'，'0'，和'106010'，SQL语句：

create table tb_temp4(t TIME);

insert into tb_temp4 values(’10:05:05’),(,23:23,),('2 10:10'),('3 02'),('10'),('101112'),('0'),('106010');

执行结果如下：

(3) DATE

DATE只用于输入日期，没有时间，在存储时需要3个字节。日期格式为：'YYYY-MM-DD' 。

·以'YYYY-MM-DD'或者'YYYYMMDD'字符串格式表示日期，取值范围是'1000-01-01’~'9999-12-03'。

·以'YY-MM-DD'或者'YYMMDD'字符串格式表示日期，取值范围与年

数相关，00~69年值范围是2000~2069，70~99年值范围是1970~1999。

·以YY-MM-DD或者YYMMDD数字格式表示日期，取值范围与年数

相关，00~69年值范围是2000~2069，70~99年值范围是1970~1999。

·使用current_date或者now()，插入当前系统日期。

案例：创建数据表tb_temp5，定义字段类型为DATE的字段d，向表中插入数值'2017-12-02'、20101010'，'99-09-09' ，'000101' ，'111111' ，121212和171203 ，SQL语句：

create table tb_temp5(d DATE);

insert into tb_temp5 values('2017-12-02'),('20101010'),('99-09-09’),('000101'),('111111'),(121212),(171203);

执行结果如下：

(4) DATETIME

DATETIME用于同时输入日期和时间信息，在存储时需要8个字节。格式为：’’YYYY-MM-DD HH:MM:SS’, 只要符合格式使用字符串和数字类型均可。

·以'YYYY-MM-DD HH:MM:SS'或者'YYYYMMDDHHMMSS'字符串格式表示日期，取值范围是'1000-01-01 00:00:00'~'9999-12-03 23:59:59' 。

·以'YY-MM-DD HH:MM:SS'或者'YYMMDDHHMMSS'字符串格式表示日期，取值范围与年数相关，00~69年值范围是2000~2069，,70~99年值范围是1970~1999。

·以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示日期，取值范围与年数相关，00~69年值范围是2000~2069，,70~99年值范围是1970~1999。

·使用now()，插入当前系统日期时间。

案例：创建数据表tb_temp6，定义字段类型为DATETIME的字段dt，向表中插入数值'2017-12-02 20:33:33'，'20101010101010'，'09-09-09 09:09:09'，'121212121212’ 和111111111111 SQL语句：

create table tb_temp6(dt DATETIME);

insert into tb_temp6 values('2017-12-02 20:33:33'),('20101010101010'),('09-09-09 09:09:09'),('121212121212'),(111111111111);

执行结果如下：

(5) TIMESTAMP

TIMESTAMP的显示格式与DATETIME相同，显示宽度是19个字符。存储需要4个字节，格式为：'YYYY-MM-DD HH:MM:SS' 。但是TIMESTAMP列的取值范围小于DATETIME的取值范围，为'1970-01-01 00:00:01'UTC~'2038-01-19 03:14:07' UTC。其中UTC(Coordinated Universal Time)世界标准时间，因此在插入数据时要保证在合法取值范围内。

案例：创建数据表tb_temp7，定义字段类型为TIMESTAMP的字段ts，向表中插入数值'20170101010101' ，'20050607080910'，'08-08-08 09:09:09'，'12@12@12 12@ 12@12'，111111111111和now()，SQL语句：

create table tb_temp7(ts TIMESTAMP);

insert into tb_temp7 values('20170101010101'),('20050607080910'),('08-08-08

09:09:09'),('12@12@12 12@12@12'),(111111111111),(now());

执行结果如下：

4. 字符串类型

字符串类型除了可以存储字符串数据以外，还可以存储其他数据，比如图片、声音和二进制数据。字符串可以进行区分或或者不区分大小写的串比较。

MySQL中的字符串char、varchar、binary、varbinary、blob、tinytext、text、enum和set。

(1) char和varchar类型

char(M)为固定长度字符串，在定义时要指定字符串列长。当保存时在右侧填充空格以达到指定长度。M表示列长度，当检索时实际值尾部空格会自动删除。

varchar(M)为长度可变的字符串，M表示列最大长度，但是保存时实际占用空间为字符串实际长度加1。不管是保存还是检索varchar的值时实际值尾部的空格都会保留。

案例：创建数据表tb_temp8，定义字段ch和vch的数据类型依次为char(4)和varchar(4)，向表中插入数据”ab “ ，SQL语句：

create table tb_temp8(ch char(4),vch varchar(4));

insert into tb_temp8 values('ab ‘,'ab ');

执行结果如下：

(2) text类型

text列保存非二进制字符串，如文章、评论等等。当保存和查询text列值时，不删除尾部空格。Text类型分为四种：tinytext、text、mediumtext和longtext。不同的text类型存储空间和数据长度不同。

·tinytext：最大长度255(28~1)个字符的text列。

·text：最大长度为65535(216~1)个字符的text列。

·mediumtext：最大长度为16777215(224~1)个字符的text列。

·longtext：最大长度为4294967295或4GB(232~1)个字符的text列。

(3) enum类型

enum是一个字符串对象，其值为创建时在列规定中枚举的一列值。Enum类型的字段取值时只能在指定的枚举列表中取，而且一次只能取一个。如果创建的成员中有空格，则其尾部的空格将自动删除。Enum值在内部用整数表示，每个枚举值均有一个索引值：列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。枚举最多可以有65535个元素。

例如：定义enum类型的列(null,",first,second,third),该列可以取值和每个值的索引如下表所示。

Enum 值依照列索引顺序排列，并且空字符排在非空字符串之前，null值排在其他所有枚举值之前。

案例：创建表tb_temp9，定义枚举类型的列enum('first' ,'second’,'third')，查看列成员的索引值，SQL语句如下：

create table tb_temp9(enm enum('first','second','third'));

insert into tb_temp9 values('first'),('second'),('third'),(null);

执行结果如下：

(4) set类型

set类型是一个字符串对象，可以有另个或者多个值，set列最多可以有64个成员，其值为创建表时规定的一系列值。Set成员各值之间用逗号,隔开。语法格式如下：

set('值1','值2',…'值n')

与enum类型相同，set的值在内部用整数表示，每一个值都有一个索引编号。当创建表时，set成员值得尾部空格将自动删除。但与enum类型不同的是，enum类型的字段只能从定义的列值中选择一个插入，而set类型的列可以从定义的列值中选择多个字符联合插入。

如果插入set字段的列值有重复，则MySQL自动删除重复值；插入set字段值顺序并不重要，MySQL会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL将忽视这些值，并给出警告。

案例：创建表tb_temp10，定义set类型字段s，取值列表为('a','b','c','d')，插入数据('a')、('a,b,a')、('c,a,d')、('a,x,b,y')。SQL语句如下：

create table tb_temp10(s set('a','b','c','d'));

insert into tb_temp10 values('a'),('a,b,a'),('c,a,d');

执行结果如下：

5. 二进制类型

MySQL支持两类字符型数据：文本字符串和二进制字符串。前面讲解了存储文本的字符串类型，今天将讲解MySQL中存储二进制数据的数据类型。

MySQL中的二进制数据类型有：bit、binary、varbinary、tinyblob、blob、mediumblob和longblob，今天讲解这些二进制类型的特点和使用方法。

(1) bit类型

bit是位字段类型。M表示值得位数，范围为1~64。如果M被省略，默认值为1。如果为bit(M)列分配的值长度小于M位，在值得左边用零填充。如bit(5)列分配一个值b'101'，其效果与分配b'00101'相同。Bit数据类型用来保存位字段值，如以二进制的形式保存数据13,13的二进制为1101，在这里需要位数至少4位的bit类型，既可以定义列类型为bit(4)。大于位数(M)的二进制数是不能插入bit(M)类型字段中的。

案例：创建表tb_temp11，定义bit(4)类型字段b,向表中插入数据3、10、18。SQL语句如下：

create table tb_temp11(b bit(4));

insert into tb_temp11 values(3),(10),(18);

执行结果如下：

(2) binary和varbinary类型

binary和varbinary类型类似于char和varchar，不同的是他们包含二进制字节字符串。使用语法如下：

列名称

binary(M)或者varbinary(M)

Binary的长度是固定的，指定长度之后，不足最大长度的，将在右边填充“\0”补齐以达到指定长度(M)。

Varbinary类型的长度是可变的，其长度可以在0到最大值之间。如制定列数据类型为varbinary(20)，如果插入的值得长度只有10，则实际存储空间为10加1，即其实际占用的空间为字符的实际长度加1。

案例：创建表tb-temp12,dingyi binary(3)类型额字段b和varbinary(3)类型的字段vb，并向表中插入数据"5"，比较两个字段的存储空间。SQL语句如下：

create table tb_temp12(b binary(3),vb varbinary(3));

insert into tb_temp12 values(5,5);

执行结果如下：

可以看到b字段的数据长度为3，而vb字段的数据长度仅为插入的一个字符长度1。B字段和vb字段的长度截然不同，因为b字段不足的空间填充了"\0"，而vb字段则没有填充。

(3) blob类型

blob是一个二进制的大对象，用来存储可变数量的数据。Blob类型分为四种：tinyblob、blob、mediumblob和longblob，他们可以容纳的最大长度不同。

Blob列存储的是二进制字符串(字节字符串)；text列存储的是非二进制字符串(字符字符串)。Blob列没有字符集，并且排序和比较基于列值字节的数值，text列有一个字符集，并且根据字符集对值进行排序和比较。

未完待续...