目录
1、前提介绍
环境：mysql5.7.30，cmd命令中进⾏演⽰。
主要内容：
- 1. 介绍mysql中常⽤的数据类型
- 2. mysql类型和java类型对应关系
- 3. 数据类型选择的⼀些建议
2、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
- 其他数据类型：暂不介绍，⽤的⽐较少
2.1、整数类型
-
INT - 正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295。 可以指定多达11位的宽度。
-
TINYINT - 一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，可以指定多达4位数的宽度。
-
SMALLINT - 一个小的整数，可以带符号。如果有符号，允许范围为-32768至32767。如果无符号，允许的范围是从0到65535，可以指定最多5位的宽度。
-
MEDIUMINT - 一个中等大小的整数，可以带符号。如果有符号，允许范围为-8388608至8388607。 如果无符号，允许的范围是从0到16777215，可以指定最多9位的宽度。
-
BIGINT - 一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 可以指定最多20位的宽度。
2.1.1、示例1：有符号类型
mysql> use test01;
Database changed
mysql> create table demo1(c1 tinyint);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into demo1 value(-pow(2,7)), (pow(2,7)-1);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from demo1;
+------+
| c1 |
+------+
| -128 |
| 127 |
+------+
2 rows in set (0.01 sec)
mysql> insert into demo1 values(pow(2,7));
ERROR 1264 (22003): Out of range value for column 'c1' at row 1
mysql>demo1表中 c1 字段为tinyint有符号类型的，可以看⼀下上⾯的演⽰，有超出范围报错的。关于数值对应的范围计算⽅式属于计算机基础的⼀些知识，可以去看⼀下计算机的⼆进制表⽰相关的⽂章。
2.1.2、示例2：⽆符号类型
mysql> create table demo2(c1 tinyint unsigned);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into demo2 valeus(-1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'valeus(-1)' at line 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 (0),(pow(2,8));
ERROR 1264 (22003): Out of range value for column 'c1' at row 2
mysql> insert into demo2 values (0),(pow(2,8)-1);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from demo2;
+------+
| c1 |
+------+
| 0 |
| 255 |
+------+
2 rows in set (0.00 sec)
mysql>c1是⽆符号的tinyint类型的，插⼊了负数会报错。
2.1.3、类型(n)说明
在开发中，我们会碰到有些定义整型的写法是int(11)，这种写法个⼈感觉在开发过程中没有什么⽤途，不过还是来说⼀下， int(N) 我们只需要记住两点：
- ⽆论N等于多少，int永远占4个字节
- N表⽰的是显⽰宽度，不⾜的⽤0补⾜，超过的⽆视长度⽽直接显⽰整个数字，但这要整型设置了unsigned zerofill 才有效
看⼀下⽰例，理解更⽅便：
mysql> CREATE TABLE test3 (
-> `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.03 sec)
mysql> insert into test3 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.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test3;
+-------+-------+-------+-------+-------+------------+------------+
| 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 test3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test3 | CREATE TABLE `test3` (
`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=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>show create table test3; 输出了表 test3 的创建语句，和我们原始的创建语句不⼀致了，原始的 d 字段⽤的是⽆符号的，可以看出当使⽤了 zerofill ⾃动会将⽆符号提升为有符号。
说明：
int(5)输出宽度不满5时，前⾯⽤0来进⾏填充
int(n)中的n省略的时候，宽度为对应类型⽆符号最⼤值的⼗进制的长度，如bigint⽆符号最⼤值为 -1 =18,446,744,073,709,551,615；长度是20位，来个bigint左边0填充的⽰例看⼀下
mysql> CREATE TABLE test4 (
-> `a` bigint zerofill
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test4 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test4;
+----------------------+
| a |
+----------------------+
| 00000000000000000001 |
+----------------------+
1 row in set (0.00 sec)
mysql>上⾯的结果中1前⾯补了19个0，和期望的结果⼀致。
2.2、浮点类型
-
FLOAT(M,D) - 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。这不是必需的，并且默认为10,2。其中2是小数的位数，10是数字(包括小数)的总数。小数精度可以到24个浮点。
-
DOUBLE(M,D) - 不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。 这不是必需的，默认为16,4，其中4是小数的位数。小数精度可以达到53位的DOUBLE。 REAL是DOUBLE同义词。
-
DECIMAL(M,D) - 非压缩浮点数不能是无符号的。在解包小数，每个小数对应于一个字节。定义显示长度(M)和小数(D)的数量是必需的。 NUMERIC是DECIMAL的同义词。
float和double在不指定精度时，默认会按照实际的精度来显⽰，⽽DECIMAL在不指定精度时，默认整数为10，⼩数为0。
2.2.1、示例1(重点)
mysql> create table test5(a float(5,2),b double(5,2),c decimal(5,2));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test5 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.01 sec)
Records: 11 Duplicates: 0 Warnings: 9
mysql> select * from test5;
+-------+-------+-------+
| 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)
mysql>结果说明（注意看）：
c是decimal类型，认真看⼀下输⼊和输出，发现decimal采⽤的是四舍五⼊
认真看⼀下 a 和 b 的输⼊和输出，尽然不是四舍五⼊，⼀脸闷逼，float和double采⽤的是四舍六⼊五成双，decimal插⼊的数据超过精度之后会触发警告。
什么是四舍六⼊五成双？
就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后⾯是否还有不为0的任何数字，如果有，则直接进位，如果没有，需要看5前⾯的数字，若是奇数则进位，若是偶数则将5舍掉。
2.2.2、示例2
我们将浮点类型的（M,D）精度和标度都去掉，看看效果：
mysql> create table test6(a float,b double,c decimal);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test6 values (1,1,1),(1.234,1.234,1.4),
-> (1.234,0.01,1.5);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 2
mysql> select * from test6;
+-------+-------+------+
| a | b | c |
+-------+-------+------+
| 1 | 1 | 1 |
| 1.234 | 1.234 | 1 |
| 1.234 | 0.01 | 2 |
+-------+-------+------+
3 rows in set (0.00 sec)
mysql>说明：
a和b的数据正确插⼊，⽽c被截断了
浮点数float、double如果不写精度和标度，则会按照实际显⽰
decimal不写精度和标度，⼩数点后⾯的会进⾏四舍五⼊，并且插⼊时会有警告!
再看⼀下下⾯代码：
mysql> select sum(a),sum(b),sum(c) from test5;
+--------+--------+--------+
| 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 test6;
+--------------------+--------------------+--------+
| sum(a) | sum(b) | sum(c) |
+--------------------+--------------------+--------+
| 3.4679999351501465 | 2.2439999999999998 | 4 |
+--------------------+--------------------+--------+
1 row in set (0.00 sec)
mysql>从上⾯sum的结果可以看出 float 、 double 会存在精度问题， decimal 精度正常的，⽐如银⾏对统计结果要求⽐较精准的建议使⽤ decimal。
2.3、日期类型
MySQL的日期和时间数据类型包括：
-
DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。 例如，1973年12月30日将被存储为1973-12-30。
-
DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如，1973年12月30日下午3:30，会被存储为1973-12-30 15:30:00。
-
TIMESTAMP - 1970年1月1日午夜之间的时间戳，到2037的某个时候。这看起来像前面的DATETIME格式，无需只是数字之间的连字符; 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)。
-
TIME - 存储时间在HH:MM:SS格式。
-
YEAR(M) - 以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2))，年份就可以为1970至2069(70〜69)。如果长度指定为4，年份范围是1901-2155，默认长度为4。
2.4、字符串类型
虽然数字和日期类型比较有意思，但存储大多数数据都可能是字符串格式。 下面列出了在MySQL中常见的字符串数据类型。
-
CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。 限定长度不是必需的，它会默认为1。
-
VARCHAR(M) - 可变长度的字符串是以长度为1到255之间字符数(高版本的MySQL超过255); 例如： VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度。
-
BLOB or TEXT - 字段的最大长度是65535个字符。 BLOB是“二进制大对象”，并用来存储大的二进制数据，如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是，排序和比较上存储的数据，BLOB大小写敏感，而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。
-
TINYBLOB 或 TINYTEXT - BLOB或TEXT列用255个字符的最大长度。不指定TINYBLOB或TINYTEXT的长度。
-
MEDIUMBLOB or MEDIUMTEXT - BLOB或TEXT列具有16777215字符的最大长度。不指定MEDIUMBLOB或MEDIUMTEXT的长度。
-
LONGBLOB 或 LONGTEXT - BLOB或TEXT列具有4294967295字符的最大长度。不指定LONGBLOB或LONGTEXT的长度。
-
ENUM - 枚举，这是一个奇特的术语列表。当定义一个ENUM，要创建它的值的列表，这些是必须用于选择的项(也可以是NULL)。例如，如果想要字段包含“A”或“B”或“C”，那么可以定义为ENUM为 ENUM(“A”，“B”，“C”)也只有这些值(或NULL)才能用来填充这个字段。
3、MySQL类型和Java类型对应关系
| 类型名称 | 显示长度 | MySQL数据库类型 | 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 |
4、数据类型选择的⼀些建议
- 选⼩不选⼤：⼀般情况下选择可以正确存储数据的最⼩数据类型，越⼩的数据类型通常更快，占⽤磁盘，内存和CPU缓存更⼩。
- 简单就好：简单的数据类型的操作通常需要更少的CPU周期，例如：整型⽐字符操作代价要⼩得多，因为字符集和校对规则(排序规则)使字符⽐整型⽐较更加复杂。
- 尽量避免NULL：尽量制定列为NOT NULL，除⾮真的需要NULL类型的值，有NULL的列值会使得索引、索引统计和值⽐较更加复杂。
- 浮点类型的建议统⼀选择decimal
- 记录时间的建议使⽤int或者bigint类型，将时间转换为时间戳格式，如将时间转换为秒、毫秒，进⾏存储，⽅便⾛索引
67
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
