MySQL充电_mysql学习笔记：四.常见数据类型

mysql的数据类型

常用的有：

整数类型：bit 、 bool 、 tinyint 、 smallint 、 mediumint 、int 、 bigint

浮点数类型：float 、 double、 decimal

字符串类型：char 、 varchar 、 tinyblob 、 blob 、 mediumblob 、 longblob 、 tinytext 、 text、 mediumtext 、 longtext

日期类型：Date 、 DateTime 、 TimeStamp 、 Time 、 Year

......

mysql最常见的的数据类型，大致可以分为三类：数值(包括整数、浮点数等等)、日期/时间和字符串(字符)类型。

varchar,char后面跟括号里一个数字，标识的是长度，是空间容量，

而int(11)，tinyint(1)，bigint(20)等的数字，表示的是最小显示位数。

在建表时，mysql会自动分配长度：int(11)、tinyint(4)、smallint(6)、mediumint(9)、bigint(20)。

整数型

类型

字节数

范围(有符号)

范围(无符号)

tinyint

1

(-128,127)

(0,255)

smallint

2

(-32768,32767)

(0，65535)

mediumint

3

(-8388608,8388607)

(0,16777215)

int或integer

4

(-2147483648,2147483647)

(0,4294967295)

bigint

8

(-263，263-1)

(0，2^64)

默认是有符号类型的，无符号的需要在类型后面跟上 unsigned

一个字节8位，二进制标识，无符号范围就是2的8*字节数的幂

所有数值数据类型(除 BIT 和 BOOLEAN 外)都可以有符号或无符号。有符号数值列可以存储正或负的数值，无符号数值列只能存储正数。默认情况为有符号，但如果你知道自己不需要存储负值，可以使用 UNSIGNED 关键字，这样做将允许你存储两倍大小的值。

有符号类型

mysql> create table demo1(

-> c1 tinyint

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> insert into demo1 values(-pow(2,7)),(pow(2,7)-1);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from demo1;

+------+

| c1 |

+------+

| -128 |

| 127 |

+------+

2 rows in set (0.00 sec)

mysql> insert into demo1 values(pow(2,7));

ERROR 1264 (22003): Out of range value for column 'c1' at row 1

#c1字段为tinyint有符号类型，超出了范围

无符号类型

无符号类型在数据类型后加unsigned

mysql> create table demo2(

-> c1 tinyint unsigned

-> );

Query OK, 0 rows affected (0.00 sec)

mysql> insert into demo2 values (-1);

ERROR 1264 (22003): Out of range value for column 'c1' at row 1

mysql> insert into demo2 values (pow(2,8)+1);

ERROR 1264 (22003): Out of range value for column 'c1' at row 1

mysql> insert into demo2 values (pow(2,8));

ERROR 1264 (22003): Out of range value for column 'c1' at row 1

mysql> insert into demo2 values (pow(2,8)-1);

Query OK, 1 row affected (0.00 sec)

mysql> insert into demo2 values (0);

Query OK, 1 row affected (0.00 sec)

mysql> select * from demo2;

+------+

| c1 |

+------+

| 255 |

| 0 |

+------+

2 rows in set (0.00 sec)

长度int(n)和zerofill

int(n) 只影响显示字符的宽度，不限制数值的合法范围。

int(3) 依然可以存储 123456789 这么大的数值。

若设置了 zerofill 属性，当 int(3) 存储 12 时，会在前面补0，补足3位。即 012；

当 int(5) 存储 12 时，会在前面补三个0，补足5位。即 00012

示例：

mysql> create table demo3 (

-> a int,

-> b int(5),

-> c int(5) unsigned,

-> d int(5) zerofill,

-> e int(5) unsigned zerofill,

-> f int zerofill,

-> g int unsigned zerofill

-> );

Query OK, 0 rows affected (0.29 sec)

mysql> insert into demo3 values(1,1,1,1,1,1,1),(11,11,11,11,11,11,11),(12345,12345,12345,12345,12345,12345,12345);

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from demo3

-> ;

+-------+-------+-------+-------+-------+------------+------------+

| a | b | c | d | e | f | g |

+-------+-------+-------+-------+-------+------------+------------+

| 1 | 1 | 1 | 00001 | 00001 | 0000000001 | 0000000001 |

| 11 | 11 | 11 | 00011 | 00011 | 0000000011 | 0000000011 |

| 12345 | 12345 | 12345 | 12345 | 12345 | 0000012345 | 0000012345 |

+-------+-------+-------+-------+-------+------------+------------+

3 rows in set (0.00 sec)

mysql> show create table demo3;

| Table | Create Table

-----------+

| demo3 | CREATE TABLE `demo3` (

`a` int(11) DEFAULT NULL,

`b` int(5) DEFAULT NULL,

`c` int(5) unsigned DEFAULT NULL,

`d` int(5) unsigned zerofill DEFAULT NULL,

`e` int(5) unsigned zerofill DEFAULT NULL,

`f` int(10) unsigned zerofill DEFAULT NULL,

`g` int(10) unsigned zerofill DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1 row in set (0.00 sec)

show create table test3; 输出了表 test3 的创建语句，和我们原始的创建语句不一致了，原始的d 字段用的是无符号的，可以看出当使用了 zerofill 自动会将无符号提升为有符号。

int(5)输出宽度不满5时，前面用0来进行填充

int(n)中的n省略的时候，宽度为对应类型无符号最大值的十进制的长度，如bigint无符号最大值为2^64-1 = 18,446,744,073,709,551,615 ；长度是20位，来个bigint左边0填充的示例看一下

测试：

mysql> create table demo4 (

-> a bigint zerofill

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> insert into demo4 values(1);

Query OK, 1 row affected (0.00 sec)

mysql> select * from demo4;

+----------------------+

| a |

+----------------------+

| 00000000000000000001 |

+----------------------+

1 row in set (0.00 sec)

#上面的结果中1前面补了19个0，和期望的结果一致。

浮点型

类型

字节大小

范围(有符号)

范围(无符号)

float[(m,d)]

4

(-3.402823466E+38,3.402823466351E+38)

[0,3.402823466E+38)

double[(m,d)]

8

(-1.7976931348623157E+308,1.7976931348623157E+308)

[0,1.7976931348623157E+308)

decimal[(m,d)]

对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2

依赖于M和D的值

依赖于M和D的值

​float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型。

​浮点型和定点型可以用类型名称后加(M，D)来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。

​float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。

示例1：

mysql> create table demo5(a float(5,2),b double(5,2),c decimal(5,2));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into demo5 values (1,1,1),(2.1,2.1,2.1),(3.123,3.123,3.123),(4.125,4.125,4.125),(5.115,5.115,5.115),(6.126,6.126,6.126),(7.116,7.116,7.116),(8.1151,8.1151,8.1151),(9.1251,9.1251,9.1251),(10.11501,10.11501,10.11501),(11.12501,11.12501,11.12501);

Query OK, 11 rows affected, 9 warnings (0.00 sec)

Records: 11 Duplicates: 0 Warnings: 9

mysql> select * from demo5;

+-------+-------+-------+

| a | b | c |

+-------+-------+-------+

| 1.00 | 1.00 | 1.00 |

| 2.10 | 2.10 | 2.10 |

| 3.12 | 3.12 | 3.12 |

| 4.12 | 4.12 | 4.13 |

| 5.12 | 5.12 | 5.12 |

| 6.13 | 6.13 | 6.13 |

| 7.12 | 7.12 | 7.12 |

| 8.12 | 8.12 | 8.12 |

| 9.13 | 9.13 | 9.13 |

| 10.12 | 10.12 | 10.12 |

| 11.13 | 11.13 | 11.13 |

+-------+-------+-------+

11 rows in set (0.00 sec)

注意：

c是decimal类型，认真看一下输入和输出，发现decimal采用的是四舍五入

下 a 和 b 的输入和输出，不是四舍五入,float和double采用的是四舍六入五成双

什么是四舍六入五成双？

就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后面是否还有不为0的任何数字，如果有，则直接进位，如果没有，需要看5前面的数字，若是奇数则进位，若是偶数则将5舍掉

示例2;

将浮点类型的(M,D)精度和标度都去掉，看看效果：

mysql> create table demo6(a float,b double,c decimal);

Query OK, 0 rows affected (0.04 sec)

mysql> insert into demo6 values (1,1,1),(1.234,1.234,1.4),(1.234,0.01,1.5);

Query OK, 3 rows affected, 2 warnings (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 2

mysql> select * from demo6;

+-------+-------+------+

| a | b | c |

+-------+-------+------+

| 1 | 1 | 1 |

| 1.234 | 1.234 | 1 |

| 1.234 | 0.01 | 2 |

+-------+-------+------+

3 rows in set (0.00 sec)

这说明：

a和b的数据正确插入，而c被截断了

浮点数float、double如果不写精度和标度，则会按照实际显示

decimal不写精度和标度，小数点后面的会进行四舍五入，并且插入时会有警告!

mysql> select sum(a),sum(b),sum(c) from demo5;

+--------+--------+--------+

| sum(a) | sum(b) | sum(c) |

+--------+--------+--------+

| 67.21 | 67.21 | 67.22 |

+--------+--------+--------+

1 row in set (0.00 sec)

mysql> select sum(a),sum(b),sum(c) from demo6;

+--------------------+--------------------+--------+

| sum(a) | sum(b) | sum(c) |

+--------------------+--------------------+--------+

| 3.4679999351501465 | 2.2439999999999998 | 4 |

+--------------------+--------------------+--------+

1 row in set (0.00 sec)

从上面sum的结果可以看出 float 、 double 会存在精度问题， decimal 精度正常的，比如银行对统计结果要求比较精准的建议使用 decimal 。

日期类型

日期时间类型

占用空间

日期格式

最小值

最大值

零值表示

DATETIME

8 bytes

YYYY-MM-DD HH:MM:SS

1000-01-01 00:00:00

9999-12-31 23:59:59

0000-00-00 00:00:00

TIMESTAMP

4 bytes

YYYY-MM-DD HH:MM:SS

19700101080001

2038 年1月19日的11:14:07

00000000000000

DATE

4 bytes

YYYY-MM-DD

1000-01-01

9999-12-31

0000-00-00

TIME

3 bytes

HH:MM:SS

-838:59:59

838:59:59

00:00:00

YEAR

1 bytes

YYYY

1901

2155

0000

DATETIME

DATETIME 用于表示 年月日 时分秒，是 DATE 和 TIME 的组合，并且记录的年份(见上表)比较长久。如果实际应用中有这样的需求，就可以使用 DATETIME 类型。

1、DATETIME列可以设置为多个，默认可为null，可以手动设置其值。

2、DATETIME列不可设定默认值。

3、DATETIME列可以变相的设定默认值，比如通过触发器、或者在插入数据时候，将DATETIME字段值设置为now()，这样可以做到了，尤其是后者，在程序开发中常常用到。

TIMESTAMP

TIMESTAMP 用于表示 年月日 时分秒，但是记录的年份(见上表)比较短暂，TIMESTAMP列必须有默认值，默认值可以为“0000-00-00 00:00:00”，但不能为null。

TIMESTAMP 和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是 不一样的。

表中的第一个 TIMESTAMP 列自动设置为系统时间(CURRENT_TIMESTAMP)。当插入或更新一行，但没有明确给 TIMESTAMP 列赋值，也会自动设置为当前系统时间。如果表中有第二个 TIMESTAMP 列，则默认值设置为0000-00-00 00:00:00。

TIMESTAMP 的属性受 Mysql 版本和服务器 SQLMode 的影响较大。

如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。

注：一般建表时候，创建时间用datetime，更新时间用timestamp。

DATE

DATE 用于表示 年月日，如果实际应用值需要保存 年月日 就可以使用 DATE。

TIME

TIME 用于表示 时分秒，如果实际应用值需要保存 时分秒 就可以使用 TIME。

YEAR

YEAR 用于表示 年份，YEAR 有 2 位(最好使用4位)和 4 位格式的年。默认是4位。如果实际应用只保存年份，那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。

字符串类型

类型

大小

用途

CHAR

0-255 bytes

定长字符串

VARCHAR

0-65535 bytes

变长字符串

TINYBLOB

0-255 bytes

不超过 255 个字符的二进制字符串

TINYTEXT

0-255 bytes

短文本字符串

BLOB

0-65 535 bytes

二进制形式的长文本数据

TEXT

0-65 535 bytes

长文本数据

MEDIUMBLOB

0-16 777 215 bytes

二进制形式的中等长度文本数据

MEDIUMTEXT

0-16 777 215 bytes

中等长度文本数据

LONGBLOB

0-4 294 967 295 bytes

二进制形式的极大文本数据

LONGTEXT

0-4 294 967 295 bytes

极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。