MySql数据类型
主要内容
- 介绍mysql中常用的数据类型
- mysql类型和java类型对应关系
MySQL的数据类型
- 字符串类型：
char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext - 日期类型：
Date、DateTime、TimeStamp、Time、Year - 整数类型：
bit、bool、tinyint、smallint、mediumint、int、bigint - 浮点数类型：
float、double、decimal
整数类型
| 类型 | 字节数 | 有符号范围 | 无符号范围 |
|---|---|---|---|
bigint | 8 | [-2^63,2^63-1] | [0,2^64-1] |
int | 4 | [-2^31,2^31-1] | [0,2^32-1] |
mediumint | 3 | [-2^23,2^23-1] | [0,2^24-1] |
smallint | 2 | [-2^15,2^15-1] | [0,2^16-1] |
tinyint | 1 | [-2^7,2^7-1] | [0,2^8-1] |
- 有符号类型
mysql> create table un1(
x tinyint
);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into un1 values(-pow(2,7)),(pow(2,7)-1);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from un1;
+------+
| x |
+------+
| -128 |
| 127 |
+------+
2 rows in set (0.02 sec)
mysql> insert into un1 values(pow(2,7));
1264 - Out of range value for column 'x' at row 1
mysql>
- 无符号类型
mysql> create table un2( x tinyint unsigned);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into un2 values (-1);
1264 - Out of range value for column 'x' at row 1
mysql> insert into un2 values (pow(2,8));
1264 - Out of range value for column 'x' at row 1
mysql> insert into un2 values (0),(pow(2,8));
1264 - Out of range value for column 'x' at row 2
mysql> insert into un2 values (0),(pow(2,8)-1);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from un2;
+-----+
| x |
+-----+
| 0 |
| 255 |
+-----+
2 rows in set (0.02 sec)
mysql>
-
类型 int(n)说明
- 无论N等于多少，int永远占4个字节
- n表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，需要整型设置了unsigned zerofill才有效
mysql> CREATE TABLE un3 ( `a1` int, `a2` int(5), `a3` int(5) unsigned, `a4` int(5) zerofill, `a5` int(5) unsigned zerofill, `a6` int zerofill, `a7` int unsigned zerofill ); Query OK, 0 rows affected (0.02 sec) mysql> insert into un3 values (5,5,5,5,5,5,5),(22,22,22,22,22,22,22),(45612,45612,45612,45612,45612,45612,45612); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from un3; +-------+-------+-------+-------+-------+------------+------------+ | a1 | a2 | a3 | a4 | a5 | a6 | a7 | +-------+-------+-------+-------+-------+------------+------------+ | 5 | 5 | 5 | 00005 | 00005 | 0000000005 | 0000000005 | | 22 | 22 | 22 | 00022 | 00022 | 0000000022 | 0000000022 | | 45612 | 45612 | 45612 | 45612 | 45612 | 0000045612 | 0000045612 | +-------+-------+-------+-------+-------+------------+------------+ 3 rows in set (0.02 sec) mysql> show create table un3; | Table | Create Table | un3 | CREATE TABLE `un3` ( `a1` int DEFAULT NULL, `a2` int DEFAULT NULL, `a3` int unsigned DEFAULT NULL, `a4` int(5) unsigned zerofill DEFAULT NULL, `a5` int(5) unsigned zerofill DEFAULT NULL, `a6` int(10) unsigned zerofill DEFAULT NULL, `a7` int(10) unsigned zerofill DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci | 1 row in set (0.03 sec) mysql>
show create table un3;输出了表un3的创建语句，原始的创建语句不一致了，原始的a4字段用的是无符号的，可以看出当使用了zerofill自动会将无符号提升为有符号。
- int(5)输出宽度不满5时，前面用0来进行填充
- int(n)中的n省略的时候，宽度为对应类型无符号最大值的十进制的长度
mysql> CREATE TABLE un4 ( `a` bigint zerofill );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into un4 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select *from un4;
+----------------------+
| a |
+----------------------+
| 00000000000000000001 |
+----------------------+
1 row in set (0.01 sec)
mysql>
字符串类型
| 类型 | 大小（字节） | 用途 |
|---|---|---|
| char | [0-2^8-1] | 定长字符串 |
| varchar | [0-2^16-1] | 变长字符串 |
| tinyblob | [0-2^8-1] | 255以内的二进制字符串 |
| tinytext | [0-2^8-1] | 短文本字符串 |
| blob | [0-2^16-1] | 二进制字符串 |
| text | [0-2^16-1] | 长文本字符串 |
| mediumblob | [0-2^24-1] | 较大二进制字符串 |
| mediumtext | [0-2^24-1] | 较长文本字符串 |
| longblob | [0-2^32-1] | 超级大二进制字符串 |
| longtext | [0-2^32-1] | 超级长文本字符串 |
- char类型占用固定长度，如果存放的数据为固定长度的建议使用char类型，如：手机号码、身份证等固定长度的信息。
- 表格中的L表示存储的数据本身占用的字节，L 以外所需的额外字节为存放该值的长度所需的字节数。
- MySQL 通过存储值的内容及其长度来处理可变长度的值，这些额外的字节是无符号整数。
日期类型
| 日期和时间类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| DATE | 4 | 1000-01-01 | 9999-12-31 |
| DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
| TIMESTAMP | 4 | 19700101080001 | 某年的某个时刻 |
| TIME | 3 | -838:59:59 | 838:59:59 |
| YEAR | 1 | 1901 | 2155 |
浮点类型
- float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型。
- 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。
- float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。
| 类型 | 大小（字节） | 无符号范围 | 有符号范围 | 用途 |
|---|---|---|---|---|
| fload | 4 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| double | 8 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| decimal | DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
mysql> create table un5(a1 float(5,2),a2 double(5,2),a3 decimal(5,2));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into un5 values (2,2,2),(2.7,2.7,2.7),(3.456,3.456,3.456),(4.789,4.789,4.789),(5.115,5.115,5.115),(6.798,6.798,6.798),(7.897,7.897,7.897),(8.8911,8.8911,8.8911),(9.321,9.321,9.321),(10.15468,10.15468,10.15468),(11.12501,11.12501,11.12501);
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 9
mysql> select * from un5;
+-------+-------+-------+
| a1 | a2 | a3 |
+-------+-------+-------+
| 2.00 | 2.00 | 2.00 |
| 2.70 | 2.70 | 2.70 |
| 3.46 | 3.46 | 3.46 |
| 4.79 | 4.79 | 4.79 |
| 5.12 | 5.12 | 5.12 |
| 6.80 | 6.80 | 6.80 |
| 7.90 | 7.90 | 7.90 |
| 8.89 | 8.89 | 8.89 |
| 9.32 | 9.32 | 9.32 |
| 10.15 | 10.15 | 10.15 |
| 11.13 | 11.13 | 11.13 |
+-------+-------+-------+
11 rows in set (0.02 sec)
mysql>
结果说明：
a3是decimal类型，认真看一下输入和输出，发现decimal采用的是四舍五入
认真看一下a1和a2的输入和输出，尽然不是四舍五入，一脸闷逼，float和double采用的是四舍六入五成双
decimal插入的数据超过精度之后会触发警告。
什么是四舍六入五成双？
就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后面是否还有不为0的任何数字，如果有，则直接进位，如果没有，需要看5前面的数字，若是奇数则进位，若是偶数则将5舍掉
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 |
