常见数据类型

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	（-2^63，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。

对于声明语法DECIMAL(M,D)，自变量的值范围如下：

M是最大位数（精度），范围是1到65。可不指定，默认值是10。
D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0。

例如字段 salary DECIMAL(5,2)，能够存储具有五位数字和两位小数的任何值，因此可以存储在salary列中的值的范围是从-999.99到999.99。

示例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 类型，可存储的最大长度不同，可根据实际情况选择。

posted @ 2021-02-01 16:49 EverEternity 阅读(633) 评论(0) 编辑收藏举报

刷新页面返回顶部

Windy Tracel Blog

若似月轮终皎洁，不辞冰雪为卿热