一 MySQL数据类型分类





mysql 正负数排序 mysql正整数类型_插入数据



由上图可以看到，MySQL的数据类型可以分为三大类：数值型，时间日期型和字符创型，接下来我们分别讲解这三种类型。



二 数值型



2.1 整数型




整数类型可以分为以下几种：


mysql 正负数排序 mysql正整数类型_mysql_02



  • 可使用unsigned控制是否有正负
  • 可以使用zerofill来进行前导零填充
  • 也存在 布尔bool类型，但是就是tinyint(1)的别名




整数：

  • TINYINT[(M)] [UNSIGNED] [ZEROFILL]
  • SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
  • MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
  • INT[(M)] [UNSIGNED] [ZEROFILL] 也可以使用 INTEGER
  • BIGINT[(M)] [UNSIGNED] [ZEROFILL]



2.1.1 无格式控制



创建一个表，表中有两种类型的数据，一种为tinyint型，另外一种为int型数据，都是有符号类型且无填充的。 


mysql> create table if not exists int_1(
    -> `numA` tinyint,
    -> `numB` int
    -> );
Query OK, 0 rows affected (0.05 sec)


向该表中插入数据 

mysql> insert into int_1 values(100, 101);
mysql> insert into int_1 values(1, 12);


mysql> insert into int_1 values(-1, 12);



mysql> select * from int_1;
+------+------+
| numA | numB |
+------+------+
|  100 |  101 |
|    1 |   12 |
|   -1 |   12 |
+------+------+
3 rows in set (0.00 sec)


从展示的数据可以看出，默认的行为是有符号类型的，没有填充的类型。



对于有符号类型，tinyint占用一个字节，取值范围为-128~127；而int占用四个字节，取值范围为-32768~32767； 



mysql> insert into int_1 values(127, 12);
Query OK, 1 row affected (0.03 sec)

mysql> insert into int_1 values(128, 12);
ERROR 1264 (22003): Out of range value for column 'numA' at row 1
mysql> insert into int_1 values(-128, 12);
Query OK, 1 row affected (0.02 sec)

mysql> insert into int_1 values(-129, 12);
ERROR 1264 (22003): Out of range value for column 'numA' at row 1

从上述内容可以看到， MySQL对于插入超出范围的数的处理方式时报错！不会像CPP语言一样溢出！！！


2.1.2 有格式控制



创建表int_2，控制显示数字的最小宽度为3，无符号类型，用前导零来填充； 


mysql> create table if not exists int_2(
    -> `numA` tinyint(3) unsigned zerofill,
    -> `numB` int(3) unsigned zerofill
    -> );
Query OK, 0 rows affected (0.06 sec)



插入数据 


mysql> insert into int_2 values(1, 1);
mysql> insert into int_2 values(12, 1234);



数据展示： 


mysql> select * from int_2;
+------+------+
| numA | numB |
+------+------+
|  001 |  001 |
|  012 | 1234 |
+------+------+
2 rows in set (0.00 sec)

从数据展示中我们可以看出，通过规定数据的 显示宽度可以达到统一显示的目的，类型（M）表示的显示的 最小宽度是多少，不会影响宽度大于M的数字。

zerofill。


注意：

  • 设置宽度显示不影响数的取值范围，数的取值范围是有数据类型决定的；
  • 设置显示宽度不会发生数据截取的现象。




2.2 小数型



mysql 正负数排序 mysql正整数类型_数据_03



  • 小数型可以用（M，D）控制数值的范围；
  • 可以是无符号的；
  • 可以zerofill；
  • 可以使用无符号数



单精度：FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]



双精度：DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
其中M表示总的位数，D表示小数位数。此M,D可以控制保存的范围。Float(10,2) -99999999.99 到 99999999.99
如果省略M,D会根据计算机硬件进行处理。 单精度，M大约为7左右；而双精度，M大约为15左右。



定点数：DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
其中M表示总的位数，D表示小数位数。此M,D可以控制保存的范围。
M，D省略，默认为10,0；

注意：可以unsigned 但是不会影响范围。


2.2.1 float类型和double类型



NO1 创建一个没有空格控制的float表 

mysql> create table if not exists float_1(
    -> `fl` float,
    -> `f2` double
    -> );
Query OK, 0 rows affected (0.06 sec)

创建一个没有格式控制的表，表中有两种数据类型，一种为float类型，另外一种为double类型，都是有符号类型，没有填充的；


向该表中插入数据： 


mysql> insert into float_1 values(12.123, 1234.5678);
mysql> insert into float_1 values(-12.123, -1234.5678);



mysql> select * from float_1;
+---------+------------+
| fl      | f2         |
+---------+------------+
|  12.123 |  1234.5678 |
| -12.123 | -1234.5678 |
+---------+------------+
2 rows in set (0.00 sec)


控制数值的范围（ 和整型不同），type（M,D）表示小数位数最多为三位，整数部分最多为M-D位，超过这么多位的情况下会提示插入数据错误，当然了，如果插入的数据超过了数据的取值范围的话也会发生错误。（ M表示所有的数值位数，不包含小数点和符号；D表示允许的小数位数）。



创建一个拥有格式控制的float表 


mysql> create table if not exists float_2(
    -> `f1` float(5,2) zerofill,
    -> `f2` double(6,3) zerofill
    -> );
Query OK, 0 rows affected (0.06 sec)


上方的表中f1中可以有五位数，小数点占两位、整数占三位，当填充的数字不足时用前导零来填充


上方的表中f2中可以有六位数，小数点占三位、整数占三位，当填充的数字不足时用前导零来填充


向里边插入数据： 

mysql> insert into float_2 values(12.34, 12.34);
mysql> insert into float_2 values(1.1, 1.2);
mysql> insert into float_2 values(123.45, 123.456);


数据展示： 

mysql> select * from float_2;
+--------+---------+
| f1     | f2      |
+--------+---------+
|  12.34 |  12.340 |
|  01.10 |  01.200 |
| 123.45 | 123.456 |
+--------+---------+
3 rows in set (0.00 sec)


注意：当插入数据的位数超过规定的额定值以后，插入出错！ 

mysql> insert into float_2 values(123456.1, 123456.34);
ERROR 1264 (22003): Out of range value for column 'f1' at row 1
mysql> insert into float_2 values(1234.12, 123456.34);
ERROR 1264 (22003): Out of range value for column 'f1' at row 1


NO2 浮点数的科学计数发（E）


浮点数支持科学计数法，如插入数据时： 


mysql> insert into float_2 values(0.1234E2, 0.123456E3);
Query OK, 1 row affected (0.02 sec)



mysql> select * from float_2;
+--------+---------+
| f1     | f2      |
+--------+---------+
|  12.34 |  12.340 |
|  01.10 |  01.200 |
| 123.45 | 123.456 |
|  12.34 | 123.456 |
+--------+---------+
4 rows in set (0.00 sec)



当向表中插入的小数点位数多余分配的位数的时候，小数点会四舍五入！


mysql> insert into float_2 values(12.126, 12.34);
Query OK, 1 row affected (0.02 sec)



|  12.13 |  12.340 |
+--------+---------+






2.2.2 定点数



decimal（M,D），其中M表示总的位数，D表示小数位数。



注意：


  • 小数超过位数的话会出现四舍五入的情况，不会报错；
  • 默认情况下，M为10，D为0；



创建一个定点数表： 


mysql> create table if not exists float_3(
    -> `f` decimal(10,4) unsigned zerofill
    -> );
Query OK, 0 rows affected (0.07 sec)


插入数据： 

mysql> insert into float_3 values(123.45);
mysql> insert into float_3 values(123.4567);
mysql> insert into float_3 values(123.45678);



mysql> select * from float_3;
+-------------+
| f           |
+-------------+
| 000123.4500 |
| 000123.4567 |
| 000123.4568 |
+-------------+
3 rows in set (0.00 sec)


2.3 时间日期


mysql 正负数排序 mysql正整数类型_插入数据_04



DATETIME，TIMESTAMP，DATE，在保存数据时，针对数据格式与合法性进行验证，不对日期时间的合法性进行验证。
年月日时分秒的分隔符可以是任意的标点，但是常用的是-和:,甚至是不使用都可以。
2位的年也是被允许的，但是表示的范围70-69 表示：1970-2069范围

TIMESTAMP：时间戳，年份是一个范围

TIME：TIME类型不仅可以用于表示一天的时间，还可以表示一个间隔时间（或者过去了多久）
因此该类型可以设置为多个小时，甚至是 几天几小时几分钟几秒的情况 D HH:II:SS。
同样在设置时分秒时，可以使用其他标点符号作为分隔符，甚至是不用（用天了就不行了）。



2.3.1 Datetime（年月日时分秒）



任意分隔符的日期，也可以不加分隔符，也就是说只要有分隔符即可！但是日期范围必须要正确，如月份不能超过12



创建一个时间表date_1： 


mysql> create table if not exists date_1(
    -> myDate datetime
    -> );
Query OK, 0 rows affected (0.05 sec)



mysql> insert into date_1 values('20160803142011');
Query OK, 1 row affected (0.02 sec)

mysql> insert into date_1 values(20160803142015);
Query OK, 1 row affected (0.01 sec)

mysql> insert into date_1 values('2016-08-03 14:20:25');
Query OK, 1 row affected (0.02 sec)


数据展示： 

mysql> select * from date_1;
+---------------------+
| myDate              |
+---------------------+
| 2016-08-03 14:20:11 |
| 2016-08-03 14:20:15 |
| 2016-08-03 14:20:25 |
+---------------------+
3 rows in set (0.00 sec)

从上方我么可以看到，插入的时间必须要在一定的范围之内，插入时间的格式多种多样，既可以是字符串（用单引号引起来），也可以是单纯的整数，同时也可以使用分隔符（使用分隔符的时候相当于使用字符串，一定要用单引号引起来）；


注意：


  • 插入的时间一定要在其允许的范围之内；
  • 分隔符可以是任意的字符，但是为了方便起见，在年份中一般用‘-’，在时间中一般用‘：’。



时间值是可以有零值的，插入零值： 


mysql> insert into date_1 values(0);



| 0000-00-00 00:00:00 |



插入的数据也可以是不完全的，例如： 


mysql> insert into date_1 values('16:08:03');
Query OK, 1 row affected (0.03 sec)



| 2016-08-03 00:00:00 |



2.3.2 时间戳timestamp（年月日时分秒/整数）



存储时用整数、字符串都可以，表示日期和时间；



任意分隔符的日期，也可以不加分隔符，也就是说只要有分隔符即可！但是日期范围必须要正确，如月份不能超过12。



创建时间戳表date_2： 


mysql> create table if not exists date_2(
    -> `t` timestamp
    -> );
Query OK, 0 rows affected (0.14 sec)


向时间戳表date_2中插入数据： 

mysql> insert into date_2 values(20160803143415);
Query OK, 1 row affected (0.02 sec)

mysql> insert into date_2 values('20160803143420');
Query OK, 1 row affected (0.02 sec)
mysql> insert into date_2 values('2016-08-03 14:34:25');
Query OK, 1 row affected (0.02 sec)



mysql> select * from date_2;
+---------------------+
| t                   |
+---------------------+
| 2016-08-03 14:34:15 |
| 2016-08-03 14:34:20 |
| 2016-08-03 14:34:25 |
+---------------------+
3 rows in set (0.00 sec)

从上方代码可以看到，时间戳和方法和datetime是一致的，这里不再详细赘述！


与datetime不同的一点是：检索列时，+0 可以检索时间戳,其实是一个整数的形式（去掉了连接符号）


其次就是表示的范围不一样，这是由所占用的字节数决定的。 


mysql> select t+0 from date_2;
+----------------+
| t+0            |
+----------------+
| 20160803143415 |
| 20160803143420 |
| 20160803143425 |
+----------------+
3 rows in set (0.00 sec)



2.3.3 date（年月日）


mysql> create table if not exists date_3(
    -> `t` date
    -> );
Query OK, 0 rows affected (0.05 sec)


插入数据： 

mysql> insert into date_3 values(20160803);
Query OK, 1 row affected (0.03 sec)

mysql> insert into date_3 values('20160803');
Query OK, 1 row affected (0.03 sec)

mysql> insert into date_3 values('2016:08:03');
Query OK, 1 row affected (0.02 sec)


数据展示： 

mysql> select * from date_3;
+------------+
| t          |
+------------+
| 2016-08-03 |
| 2016-08-03 |
| 2016-08-03 |
+------------+
3 rows in set (0.00 sec)


其实上述三种时间数据类型还支持2为年份的插入方式（ 不建议使用）： 

mysql> insert into date_3 values('16:08:03');
Query OK, 1 row affected (0.03 sec)
mysql> insert into date_3 values('80:08:03');
Query OK, 1 row affected (0.03 sec)



| 2016-08-03 |
| 1980-08-03 |



70-69 1970 - 2069

70-99 19xx（默认）

0-69 20xx年（默认）


语法上没有错误，但是不建议使用！

2.3.4 time（时间）



表示的意义：


1，一天中的时间
2，表示时间间隔，在表示间隔时（也就是过去了多长时间），
可以使用天来表示。格式：
D HH:MM:SS
D表示天（最大为34）


创建时间表date_4: 


mysql> create table if not exists date_4(
    -> `t` time
    -> );
Query OK, 0 rows affected (0.07 sec)


插入数值： 

mysql> insert into date_4 values('14:55:27');
Query OK, 1 row affected (0.03 sec)

mysql> insert into date_4 values('5 14:55:27');
Query OK, 1 row affected (0.02 sec)



mysql> select * from date_4;
+-----------+
| t         |
+-----------+
| 14:55:27  |
| 134:55:27 |
+-----------+
2 rows in set (0.00 sec)

第一条表示插入的时间数值；

第二条表示已经过去了多长时间。



2.3.5 year（年）



由于year只占用一个字节，只能表示1901-2155的取值范围。



创建一个year表date_5： 


mysql> create table if not exists date_5(
    -> `y` year
    -> );
Query OK, 0 rows affected (0.09 sec)



插入1234，这时候会提示数据越界： 


mysql> insert into date_5 values(1234);
ERROR 1264 (22003): Out of range value for column 'y' at row 1


插入正常范围内的数据： 

mysql> insert into date_5 values(1999);
Query OK, 1 row affected (0.02 sec)

mysql> insert into date_5 values(2016);
Query OK, 1 row affected (0.02 sec)


数据展示： 

mysql> select * from date_5;
+------+
| y    |
+------+
| 1999 |
| 2016 |
+------+
2 rows in set (0.00 sec)



2.4 字符串类型



mysql 正负数排序 mysql正整数类型_插入数据_05




Char & varchar
CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。

Char,定长字符串，保存时如果字符串长度不够，则后边补足空字符串；但是在读取到数据是，会截取后边所有的字符串。因此如果真实数据存在左边空格，则需要注意。
varchar，变长字符串。在保存字符串时，同时保存该字符串的长度，小于255采用一个字节保存，否则采用二个字节保存。不会像char一样截取空格。

在定义类型长度时，同时一条记录的总长度是 65535.因此所有字段的总长度不能超过改值。同时每条记录还需要一个字节保存是否有null值，如果都没有null（都不允许为null）则该字节省略。

枚举
ENUM('value1','value2',...)
枚举值应该在值列表内，允许使用下标方式标识。1标识第一个元素，逐个递增。
除此之外，允许null和空字符串（下标为0）
下标方式，可以使用在检索中。

集合
Set(‘value1’,’value2’,…);
表示可以选择可用值的0个或多个组合。

二进制类型：
如果需要保存一个二进制文件内容的话，应该保存成这些类型，例如图片内容。


2.4.1 char & varchar类型



char（M）表示固定长度；


varchar（M）表示可变长度。


其中M表示允许的字符串的长度，M在varchar中的表示，是允许的最大长度；char内M表示严格限定的长度。


 

char(5)

varchar(5)                      

 

‘’       

5个字符                      

1个字符                                 

varchar需要一个字节保存字符串总长度

‘abc’                

5

4

 

‘abcde’  

5

6

 

 

 

 

 



M表示的是字符数，而不是字节数。但是，总的长度的使用是按照 字节计算的。


存储格式取决于当前数据库的字符集：
utf8中一个字符占用三个字节21845*3 = 65536
gbk中一个字符占用两个字节32767*2 = 65534



常见一个最大长度字符表str_1： 


mysql> create table if not exists str_1(
    -> `c` char(255)
    -> );
Query OK, 0 rows affected (0.07 sec)


创建一个最大（65535）的varchar数据类型表（ 失败）： 

mysql> create table if not exists str2(
    -> `c` varchar(65535)
    -> )character set latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to cha
ge some columns to TEXT or BLOBs
mysql>

因为varchar类型除了还要保存额外总的字符个数，除了类型本身的限制之外，还有记录总的长度；


创建成功的最大实例（字符集latin1只占用一个字符）： 


mysql> create table if not exists str2(
    -> `c` varchar(65533) not null
    -> )character set latin1;
Query OK, 0 rows affected (0.09 sec)


创建不成功（ 超过255个字符，就要用两个字节保存字符个数，且null也占用一个字符）： 

mysql> create table if not exists str3(
    -> `c` varchar(65533)
    -> )character set latin1;
ERROR 1118 (42000): Row size too large. The m
ge some columns to TEXT or BLOBs
mysql>


创建成功： 

mysql> create table if not exists str3(
    -> `c` varchar(65532)
    -> )character set latin1;
Query OK, 0 rows affected (0.09 sec)



真是的varchar长度：

记录存在总长度 65535限制。
varchar特点，当 类型数据超过255个字符时，采用2个字节表示长度。
65535-2=65533
整条记录，需要一个额外的字节，用于保存当前字段的null值。
除非所有的字段都比不是nul，这个字节才可以省略。一个记录，不论有多少个字段
存在null，都是使用统一的一个字节来表示。而不是每个字段一个字节。

创建不成功，因为下边的tinyint也要占用一个null字节： 

mysql> create table if not exists str4(
    -> `c` varchar(65533) not null,
    -> `i` tinyint
    -> )character set latin1;
ERROR 1118 (42000): Row size too large. The maximum
ge some columns to TEXT or BLOBs


2.4.2 test



能保存的长度2^16，总长度2^16+2，需要额外的两个字节
文本，有很多 兄弟类型（不用指定长度）Tinytext longtext
表示的字符串长度不一样。



2.4.3 枚举（enum）



从1开始计数），最多允许有65535个枚举项，也就是2个字节（但是会有一些其他的消耗，最终不能达到65536长度



创建枚举表enmu_1: 


mysql> create table if not exists enum_1(
    -> `e` enum('female', 'male')
    -> );
Query OK, 0 rows affected (0.06 sec)


插入数据方式1： 

mysql> insert into enum_1 values('female');
Query OK, 1 row affected (0.02 sec)

mysql> insert into enum_1 values('male');
Query OK, 1 row affected (0.02 sec)



插入数据方式2： 


mysql> insert into enum_1 values(2);
Query OK, 1 row affected (0.02 sec)

mysql> insert into enum_1 values(1);
Query OK, 1 row affected (0.01 sec)



数据展示1： 

mysql> select * from enum_1;
+--------+
| e      |
+--------+
| female |
| male   |
| male   |
| female |
+--------+
4 rows in set (0.00 sec)



数据展示2： 


mysql> select e+0 from enum_1;
+------+
| e+0  |
+------+
|    1 |
|    2 |
|    2 |
|    1 |
+------+
4 rows in set (0.00 sec)




2.4.4 set集合（相当于多项选择题）



创建set表： 


mysql> create table if not exists set_1(
    -> `s` set('A', 'B', 'C', 'D')
    -> );
Query OK, 0 rows affected (0.06 sec)



mysql> insert into set_1 values('A');
Query OK, 1 row affected (0.03 sec)

mysql> insert into set_1 values('A,B');
Query OK, 1 row affected (0.02 sec)

mysql> insert into set_1 values('C');
Query OK, 1 row affected (0.01 sec)

mysql> insert into set_1 values('C,D');
Query OK, 1 row affected (0.01 sec)



mysql> select * from set_1;
+------+
| s    |
+------+
| A    |
| A,B  |
| C    |
| C,D  |
+------+



mysql> select s+0 from set_1;
+------+
| s+0  |
+------+
|    1 |
|    3 |
|    4 |
|   12 |
+------+
4 rows in set (0.00 sec)


数据展示2的分析：set总共占据8个字节，也就是64为二进制数字，列表中每个参数占据一位，也就是说最多保存64中状态。

上述参数中的二进制存储方式为（1,10,100,1000），所以会出现数据展示2中的情况。

总结：



mysql 正负数排序 mysql正整数类型_插入数据_06



  • 应该使用最精确的类型。占用的空间少。
  • 还应该考虑到相关应用语言的处理。例如常常将时间日期保存成一个整型。便于计算。
  • 考虑移植的兼容性。