在上一篇博客中，我们简单的了解了MySQL中的常用数据类型以及约束条件，本篇博客我们来对常用的数据类型做一个详细的介绍
 
这里做个简单的回顾：
 
MySQL数据类型
 
MySQL支持多种类型的SQL数据类型：数值，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型等
 数据类型描述使用以下约定：
 
M表示整数类型的最大显示宽度。M表示整数类型的最大显示宽度。对于浮点和定点类型， M是可以存储的总位数（精度）。对于字符串类型， M是最大长度。允许的最大值M取决于数据类型。
D适用于浮点和定点类型，并指示小数点后面的位数。最大可能值为30，但不应大于 M-2。
[ ] 表示类型定义的可选部分。
 
在MySQL中常用数据类型主要分为以下几类
 
数值类型
字符串类型
日期时间类型
 
 
约束条件
 
约束条件就是在给字段加一些约束，使该字段存储的值更加符合我们的预期。
 
常用约束条件有以下这些
 
UNSIGNED ：无符号，值从0开始，无负数
ZEROFILL：零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
NOT NULL：非空约束，表示该字段的值不能为空
DEFAULT：表示如果插入数据时没有给该字段赋值，那么就使用默认值
PRIMARY KEY：主键约束，表示唯一标识，不能为空，且一个表只能有一个主键。一般都是用来约束id
AUTO_INCREMENT：自增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
UNIQUE KEY：唯一值，表示该字段下的值不能重复，null除外。比如身份证号是一人一号的，一般都会用这个进行约束
FOREIGN KEY：外键约束，目的是为了保证数据的完成性和唯一性，以及实现一对一或一对多关系
 
 
数值类型
 
数值类型包括整数型、浮点型、定点型
 
整数型（精确值）
 
TINYINT[(M)] [UNSIGNED] [ZEROFILL] 范围非常小的整数，有符号的范围是 -128到127，无符号的范围是0到 255
SMALLINT[(M)] [UNSIGNED] [ZEROFILL] 范围较小的整数，有符号的范围是 -32768到32767，无符号的范围是0到 65535
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] 中等大小的整数，有符号的范围是 -8388608到8388607，无符号的范围是0到 16777215。
INT[(M)] [UNSIGNED] [ZEROFILL] 正常大小的整数，有符号的范围是 -2147483648到 2147483647。无符号的范围是 0到4294967295。
BIGINT[(M)] [UNSIGNED] [ZEROFILL] 大整数，有符号的范围是 -9223372036854775808到 9223372036854775807，无符号的范围是0到 18446744073709551615。
 
 
上面出现了两个词，有符号和无符号。
 我们来看看 有符号和无符号（UNSIGNED） 是啥玩意
 
在计算机中，可以区分正负的类型，称为有符号类型。无正负的类型，称为无符号类型。
 简单的理解为就是
 有符号值可以表示负数，0以及正数
 无符号值只能为0或正数
 
关于有符号和无符号详解可以看这篇文章或者自己百度百科。
 
这里我们就不关心原理了，我们只需要知道有符号数可以表示负数，无符号数只能为非负数即可，如果不手动指定UNSIGNED，那么默认就是有符号的
 
下面我们用一下整数型数据
 首先创建一个表
 
CREATE TABLE int_db(
a TINYINT,
b SMALLINT,
c MIDDLEINT,
d INT,
e BIGINT
);
 
查看表结构
 
 
我们来看看type这一列，可以看到，每个字段类型后面都有一个括号，括号里面的有个数值，这个数值实际上就是字段的显示宽度，也就是M的值，M表示整数类型的最大显示宽度。最大显示宽度为255.显示宽度与类型可包含的值范围无关
 
我们在创建表的时候并没有指定字段类型的显示宽度，那么，默认的显示宽度则是该字段类型最大的显示宽度
 
例如字段a的显示宽度为4，是因为TINYINT有符号值的范围是-128到127，
 -128的长度为4（负号、1、2、8共四位），所以默认的显示宽度最大为4，其他的以此类推
 
下面我们再新建一个表，将字段a的修改为无符号类型的。再看看a字段的默认显示宽度
 
 
可以看到，默认显宽度就变成3了，因为无符号的TINYINT的值范围为0-255，没有负号，所以最多是3位。
 
ZEROFILL
 下面我们来试试ZEROFILL约束，前面的博客中我们知道。使用该约束后当数据的长度比我们指定的显示宽度小的时候会使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
 
下面我们新建个表试一下，这次我们来指定一下显示宽度
 
CREATE TABLE int_db2(
a TINYINT(8) ZEROFILL,
b TINYINT(5) UNSIGNED);
 
然后插入一条记录：
 
INSERT int_db2() VALUES(12,12);
 

 可以看到，12变成了00000012，自动在前面补了0，这是因为指定的显示宽度是8，但是12只有两位，所以在前面补0，使长度为8。这就是ZEROFILL的效果
 
浮点型
 
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
 一个小的（单精度）浮点数。允许值是-3.402823466E+38 到-1.175494351E-38， 0以及1.175494351E-38 到3.402823466E+38,M是总位数，D是小数点后面的位数。
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
 正常大小（双精度）浮点数。允许值是 -1.7976931348623157E+308到-2.2250738585072014E-308，0以及 2.2250738585072014E-308到 1.7976931348623157E+308。M是总位数，D是小数点后面的位数
 
下面我们来用一下浮点型
 创建表
 
CREATE TABLE float_db(
a FLOAT(3,2),
b DOUBLE(5,3)
)
 
我们指定a字段为FLOAT类型，总长度为3，小数点后两位为2,b字段总长度为5，小数点后两位长度为3
 
 
插入数据
 
INSERT float_db VALUES(1.111,2.113);
 
 
可以看到，我们给a字段的值是1.111，但是只存进去了1.11
 浮点数存在精度丢失的问题，如果涉及到小数运算，尽量不要用浮点型
 
定点型
 
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
 常用于存储精确的小数，M是总位数，D是小数点后的位数。小数点和（负数） -符号不计入 M。如果 D为0，则值没有小数点或小数部分。最大位数（M）为 65. 最大支持小数（D）为30.如果D省略，则默认值为0.如果M省略，则默认值为10。M的范围是1到65。D范围为0到30，且不得大于M。
 
我们来用一下DECIMAL类型
 
首先创建表，先不指定M和D
 
CREATE TABLE decimal_db(a DECIMAL);
 
 
可以看到，默认的总长度（M）为10，小数点位数(D)默认为0.
 
插入一条数据
 
INSERT decimal_db VALUES(30.556)
 
 
可以看到，存进去的数值被四舍五入阶段了，也就是说，DECIMAL也在存储时存在精度丢失的问题。
 
超出范围和溢出处理
 
当MySQL将值存储在超出列数据类型允许范围的数值列中时，结果取决于当时生效的SQL模式：
 如果启用了严格的SQL模式，则MySQL会根据SQL标准拒绝带有错误的超出范围的值，并且插入失败。
 如果未启用限制模式，MySQL会将值截断到列数据类型范围的相应端点，并存储结果值，并产生一个警告
 
在我们的配置文件中可以看到SQL模式的配置，关于SQL模式详情请看SQL模式官方文档
 
 
 
字符串类型
 
常用的字符串类型有如下
 
CHAR[(M)] 一个固定长度的字符串，在存储时始终用空格填充指定长度。 M表示以字符为单位的列长度。M的范围为0到255.如果M省略，则长度为1，存储时占用M个字节
VARCHAR(M)可变长度的字符串，M 表示字符的最大列长度，M的范围是0到65,535，存储时占用**L+1（L<=M,L为实际字符的长度）**个字节
TINYTEXT[(M)] 不能有默认值，占用L+1个字节，L<2^8
TEXT[(M)] 不能有默认值，占用L+2个字节，L<2^16
MEDIUMTEXT[(M)] 不能有默认值，占用L+3个字节，L<2^24
LONGTEXT[(M)] 不能有默认值，占用L+4个字节，L<2^32
ENUM('value1','value2',...) ENUM是一个字符串对象，其值从允许值列表中选择，它只能有一个值，从值列表中选择,最多可包含65,535个不同的元素
SET('value1','value2',...) 字符串对象，该对象可以有零个或多个值，最多可包含64个不同的成员
 
CHAR和VARCHAR
 
创建表
 
CREATE TABLE str_db(
a CHAR(4),
b VARCHAR(4));
 
插入数据
 
INSERT str_db() VALUES("","");
INSERT str_db() VALUES("ab","ab");
INSERT str_db() VALUES("abcd","abcd");
INSERT str_db() VALUES("abcdefg","abcdefg");//在严格模式下，该条数据会插入失败，非严格模式则会对数据进行截取
 
 
我们看到查询的结果是一样的，但实际上他们存储时占用的长度是不一样的。
 CHAR类型不管存储的值的长度是多少，都会占用M个字节，而VARCHAR则占用实际长度+1个字节。
 
 
但是CHAR的查询效果要高于VARCHAR，所以说，如果字段的长度能够确定的话，比如手机号，身份证号之类的字段，可以用CHAR类型，像地址，邮箱之类的就用VARCHAR
 
TEXT系列
 
TEXT系列的存储范围比VARCHAR要大，当VARCHAR不满足时可以用TEXT系列中的类型。需要注意的是TEXT系列类型的字段不能有默认值，在检索的时候不存在大小写转换，没有CHAR和VARCHAR的效率高
 
 
ENUM
 
枚举类型
 创建表
 
CREATE TABLE enum_db(gender ENUM("男","女"));
 
 
插入数据
 
INSERT enum_db() VALUES("男");
INSERT enum_db() VALUES(1); 也可以使用编号插入值，等同于"男"，序号从1开始
INSERT enum_db() VALUES("女");
INSERT enum_db() VALUES(2);等同于"女"
 
 
下面我们插入一条不是枚举集合中的数据试一下
 
 
可以看到是插入失败的
 
SET
 
在ENUM中我们只能从允许值列表中给字段插入一个值，而在SET类型中可以给字段插入多个值
 
创建表
 

CREATE TABLE set_db(
a SET('1','2','3','4','5')

)
 
 
插入数据
 

INSERT set_db() VALUES('1')
INSERT set_db() VALUES('1,2,3')
 
 
 
日期时间类型
 
TIME 范围是’-838:59:59.000000’ 到’838:59:59.000000’
DATE 支持的范围是 '1000-01-01’到 ‘9999-12-31’
DATETIME 日期和时间组合。支持的范围是 '1000-01-01 00:00:00.000000’到 ‘9999-12-31 23:59:59.999999’。
TIMESTAMP 时间戳。范围是’1970-01-01 00:00:01.000000’UTC到’2038-01-19 03:14:07.999999’UTC。
YEAR 范围是 1901到2155
 
TIME
 
我们可以看到TIME的存储范围是’-838:59:59’到 ‘838:59:59’，因为TIME类型不仅可以用于表示一天中的时间（，还可以用于表示两个事件之间的经过时间或时间间隔
 
TIME的完整的显示为 D HH:MM:SS
 D：表示天数，当指定该值时，存储时小时会先乘以该值
 HH：表示小时
 MM：表示分钟
 SS:表示秒
 
创建表：
 
CREATE TABLE time_db(
a TIME
)
 
插入值：
 
INSERT time_db() VALUES('22:14:16');
--   -2表示间隔了2两天
INSERT time_db() VALUES('-2 22:14:16');
-- 有冒号从小时开始
INSERT time_db() VALUES('14:16');
-- 没有冒号且没有天数则数据从秒开始
INSERT time_db() VALUES('30');
-- 有天数也从小时开始
INSERT time_db() VALUES('3 10');
-- 直接使用数字代替也可以
INSERT time_db() VALUES(253621);
 
 
DATE
 
创建表
 
CREATE TABLE date_db(
a DATE)
 
插入数据
 
INSERT date_db() VALUES(20180813);
INSERT date_db() VALUES("2018-06-1");
INSERT date_db() VALUES("2018-4-1");
INSERT date_db() VALUES("2018-04-07");
 
 
DATETIME
 
创建表
 
CREATE TABLE datetime_db(
a DATETIME
)

 
插入数据
 
INSERT datetime_db() VALUES(20180102235432);
INSERT datetime_db() VALUES("2015-04-21 21:14:32");
INSERT datetime_db() VALUES("2015-04-23");
 
 
TIMESTAMP
 
TIMESTAMP和DATETIME使用上差不多，但是范围相对较小。
 
创建表
 
CREATE TABLE timestamp_db(
a TIMESTAMP
)

 
插入数据
 
INSERT timestamp_db() VALUES(20020121);
INSERT timestamp_db() VALUES(20020121142554);
INSERT timestamp_db() VALUES("2015-12-16 21:14:15");
INSERT timestamp_db() VALUES("2015-12-17");
INSERT timestamp_db() VALUES(NULL);
INSERT timestamp_db() VALUES(CURRENT_TIMESTAMP);
INSERT timestamp_db() VALUES();
 
 
YEAR
 
创建表
 
CREATE TABLE year_db(
a YEAR
)
 
插入数据
 
INSERT year_db() VALUES("1993");
INSERT year_db() VALUES(1993);
 
 
 
MySQL中的常用数据类型大概就这些，我们在建表的时候要选择合适的数据类型定义字段。
 
下一篇：
 
MySQL表数据的增删改（DML）
 
 
如果你觉得本文对你有帮助，麻烦动动手指顶一下，可以帮助到更多的开发者，如果文中有什么错误的地方，还望指正，转载请注明转自喻志强的博客 ，谢谢！

Mysql支持的多种数据类型主要有：数值数据类型、日期/时间类型、字符串类型。
 
 
 
 
整数
 
 
浮点数&定点数
 
 
注：定点数以字符串形式存储，对精度要求高时使用decimal较好；尽量避免对浮点数进行减法和比较运算。 
 
时间/日期类型
 
 
字符串类型
 
 
blog-二进制大对象，用来存储可变数量的数据。
 
                                                                                                                                                                                             
 
 
 
int(11)
 
 
 
    在SQL语句中int代表你要创建字段的类型，int代表整型，11代表字段的长度。
 　这个11代表显示宽度，整数列的显示宽度与mysql需要用多少个字符来显示该列数值，与该整数需要的存储空间的大小都没有关系，比如，不管设定了显示宽度是多少个字符，bigint都要占用8个字节。
 　　int是整型，(11)是指显示字符的长度，但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加0
 　　声明整型数据列时，我们可以为它指定个显示宽度M(1~255)，如INT(5)，指定显示宽度为5个字符,如果没有给它指定显示宽度，MySQL会为它指定一个默认值。显示宽度只用于显示，并不能限制取值范围和占用空间，如：INT(3)会占用4个字节的存储空间，并且允许的最大值也不会是999,而是 INT整型所允许的最大值。
 
-------------------------------------------------------------------------------------
 
 
 
mysql日期和字符相互转换方法
 
date_format(date,'%Y-%m-%d %H:%i:%s')    -------------->oracle中的to_char();
 
str_to_date(date,'%Y-%m-%d %H:%i:%s')     -------------->oracle中的to_date();

 %Y：代表4位的年份
 %y：代表2为的年份
  
 %m：代表月, 格式为(01……12)  
 %c：代表月, 格式为(1……12)
  
 %d：代表月份中的天数,格式为(00……31)  
 %e：代表月份中的天数, 格式为(0……31) 
  
 %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) 
 
 
-----------------------------------------------------------------
 
mysql 取一条离当前时间最近的记录
 # mysql写法
 SELECT *, ABS(NOW() - startTime)  AS diffTime
 FROM PolicyShuPrice
 ORDER BY diffTime ASC
 
LIMIT 0, 1
 
###########
 
select  t.road_length, t.practitioner_num,  t.stations_num,
 t.bridge_num, t.tunnel_num, date_format(t.index_time,'%Y-%m-%d %h:%i:%s')
 from ix_road_basic_data  t
 where t.index_time = (select max(index_time) from ix_road_basic_data)
 limit 1
 
# oracle写法
 SELECT * FROM (
     SELECT *, ABS(NOW() - startTime) AS diffTime
     FROM PolicyShuPrice
     ORDER BY diffTime ASC 
 
) C WHERE rownum=1
 
 
 
MYSQL类型与JAVA类型对应表：
 
类型名称
显示长度
数据库类型
JAVA类型
JDBC类型索引(int)
VARCHAR
L+N
VARCHAR
java.lang.String
12
CHAR
N
CHAR
java.lang.String
1
BLOB
L+N
BLOB
java.lang.byte[]
-4
TEXT
65535
VARCHAR
java.lang.String
-1
INTEGER
4
INTEGER UNSIGNED
java.lang.Long
4
TINYINT
3
TINYINT UNSIGNED
java.lang.Integer
-6
SMALLINT
5
SMALLINT UNSIGNED
java.lang.Integer
5
MEDIUMINT
8
MEDIUMINT UNSIGNED
java.lang.Integer
4
BIT
1
BIT
java.lang.Boolean
-7
BIGINT
20
BIGINT UNSIGNED
java.math.BigInteger
-5
FLOAT
4+8
FLOAT
java.lang.Float
7
DOUBLE
22
DOUBLE
java.lang.Double
8
DECIMAL
11
DECIMAL
java.math.BigDecimal
3
BOOLEAN
1
同TINYINT
 
 
ID
11
PK (INTEGER UNSIGNED)
java.lang.Long
4
DATE
10
DATE
java.sql.Date
91
TIME
8
TIME
java.sql.Time
92
DATETIME
19
DATETIME
java.sql.Timestamp
93
TIMESTAMP
19
TIMESTAMP
java.sql.Timestamp
93
YEAR
4
YEAR
java.sql.Date
91

MySQL 提供的内置函数cast 和 convert 可以转换数据的类型
 
但是要特别注意,可以转换的数据类型是有限制的。这个类型可以是以下值其中的一个：
 
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
 
1、CAST(value as type) 就是CAST(xxx AS 类型)
 
字符串转整数:
 
mysql> select cast('sdf222222' as signed);           
+-----------------------------+
| cast('sdf222222' as signed) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)
 
浮点转字符串
 
mysql> select cast(23423423.2323 as char );      
+------------------------------+
| cast(23423423.2323 as char ) |
+------------------------------+
| 23423423.2323                |
+------------------------------+
1 row in set (0.00 sec)
 
浮点转字符串并且取别名
 
mysql> select cast(23423423.2323 as char ) as sss;
+---------------+
| sss           |
+---------------+
| 23423423.2323 |
+---------------+
1 row in set (0.00 sec)
 
2、CONVERT(value, type) 就是CONVERT(xxx,类型)
 
浮点转字符串并且取别名
 
mysql> select convert(23423423.2323,char ) as sss;       
+---------------+
| sss           |
+---------------+
| 23423423.2323 |
+---------------+
1 row in set (0.00 sec)
 
浮点转整数
 
mysql> select convert(23423423.2222,signed ) ;
+--------------------------------+
| convert(23423423.2222,signed ) |
+--------------------------------+
|                       23423423 |
+--------------------------------+
1 row in set (0.00 sec)
 
字符串转整数
 
mysql> select convert('234sssss2',signed) ;         
+-----------------------------+
| convert('234sssss2',signed) |
+-----------------------------+
|                         234 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

作为一个DBA，学习任何一个数据库时，除了掌握这个数据库运行原理外，最基础的也就是他定义的数据类型：
 
Oracle的数据类型，如果我不熟悉的，我会对照版本先查看官方文档，然后mail告诉我们的程序人员如何用，mysql也集成这种方法。
 
官方文档地址：http://dev.mysql.com/doc/
 
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型；
 
 
详细介绍，http://www.w3cschool.cc/mysql/mysql-data-types.html
 
以后有特殊用途的，我再传上来！