MYSQL数据类型

最新推荐文章于 2023-04-19 17:27:04 发布

小小瓶

最新推荐文章于 2023-04-19 17:27:04 发布

阅读量269

点赞数 2

分类专栏：数据库文章标签： mysql sql

本文链接：https://blog.csdn.net/BottleOfWalking/article/details/111407820

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

综述

在这里插入图片描述

数值

整型

类型	大小	范围（有符号）	范围（无符号）
TINYINT	1字节	(-2^7，27-1)	(0，2^8-1)
SMALLINT	2 字节	(-2^15，215-1)	(0，2^16-1)
MEDIUMINT	3 字节	(-2^23，223-1)	(0，2^24-1)
INT或INTEGER	4 字节	(-2^31，231-1)	(0，2^32-1)
BIGINT	8 字节	(-2^63，263-1)	(0，2^64-1)

在创建整型字段表的时候，默认是有符号的，如：

-- 创建表
mysql> CREATE TABLE `int_test` (
    ->   `name` varchar(255) DEFAULT NULL,
    ->   `height` tinyint(4) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.05 sec)

-- 插入有符号数据
mysql> insert into int_test VALUES('aa', -128);
Query OK, 1 row affected (0.02 sec)

mysql> insert into int_test VALUES('aa', 127);
Query OK, 1 row affected (0.00 sec)

-- 插入 无符号范围的数据， 报错
mysql> insert into int_test VALUES('aa', 128);
ERROR 1264 (22003): Out of range value for column 'height' at row 1
mysql> select * from int_test;
+------+--------+
| name | height |
+------+--------+
| aa   |   -128 |
| aa   |    127 |
+------+--------+
2 rows in set (0.00 sec)

mysql>

当需要创建无符号字段时，需要在创建表的时候，对应字段加 UNSIGNED, 如：

-- 创建无符号字段表
mysql> CREATE TABLE `int_test_unsigned` (
    ->   `name` varchar(255) DEFAULT NULL,
    ->   `height` tinyint(4) UNSIGNED DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.03 sec)
-- 插入数据
mysql> insert into int_test_unsigned VALUES('aa', 127);
Query OK, 1 row affected (0.01 sec)

mysql> insert into int_test_unsigned VALUES('aa', 128);
Query OK, 1 row affected (0.01 sec)

-- 这时插入有符号数据 会报错
mysql> insert into int_test_unsigned VALUES('aa', -128);
ERROR 1264 (22003): Out of range value for column 'height' at row 1
mysql> select * from int_test_unsigned;
+------+--------+
| name | height |
+------+--------+
| aa   |    127 |
| aa   |    128 |
+------+--------+
2 rows in set (0.00 sec)

mysql>

浮点型

类型	大小	范围（有符号）	范围（无符号）
FLOAT	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的值

浮点型申明语法：

float(m,d)

double(m,d)

decimal(m,d)

其中，

M是数字的最大数（精度）。其范围为1～65（在较旧的MySQL版本中，允许的范围是1～254）

D是小数位的个数（标度），其范围是0～30，但不得超过M

特别注意：

如 -123.45 这个数字，

在 mysql -3.23版本之前，M为7，D为2

在mysql -3.23版本之后，将符号位（+、-）和小数点位（.）不计算到M的数值中，即 M为5，D为2

decimal - 存储精确的数值，如：货币数据

(1) 存储空间:

MySQL使用二进制格式存储DECIMAL值,

每9位数字占4个字节, 剩余数字所需的存储如下表：

剩余数字	位
0	0
1–2	1
3–4	2
5–6	3
7-9	4

例如：

DECIMAL(19,9) - 小数9位占4个字节，整数 10位占4个字节，剩余1位，占1个字节，

即共（4+4+1）=9个字节

(2) 其属性有：

UNSIGNED：无符号

ZEROFILL：零填充

创建表字段时，默认是有符号，数字前没有0填充的，如果需要创建无符号 decimal 字段，或需要0填充

需要增加对应属性

-- 创建默认有符号decimal, 无填充的字段表
mysql> CREATE TABLE `decimal_test` (
    ->   `name` varchar(255) DEFAULT NULL,
    ->   `score` decimal(10,4) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.03 sec)

-- 插入数据
mysql> insert into decimal_test VALUES('aa', 2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into decimal_test VALUES('aa', 2.0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into decimal_test VALUES('aa', 2.1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into decimal_test VALUES('aa', 2.45341413);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into decimal_test VALUES('aa', -2.45341413);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from decimal_test;
+------+---------+
| name | score   |
+------+---------+
| aa   |  2.0000 |
| aa   |  2.0000 |
| aa   |  2.1000 |
| aa   |  2.4534 |
| aa   | -2.4534 |
+------+---------+
5 rows in set (0.00 sec)

mysql>

特别说明：

在Navicat 中查询的结果，小数部分不进行0补充，这只是navicat本身的问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YOK4OVbr-1608369891924)(img/image-20201218011830168.png)]

有时候，业务场景可能需要无符号、数字前面0填充的情况，如：

-- 创建默认有符号decimal, 无填充的字段表
mysql> CREATE TABLE `decimal_test_zerofill` (
    ->   `name` varchar(255) DEFAULT NULL,
    ->   `score` decimal(10,4) zerofill unsigned DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.05 sec)

-- 插入数据
mysql> insert into decimal_test_zerofill VALUES('aa', 2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into decimal_test_zerofill VALUES('aa', 2.0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into decimal_test_zerofill VALUES('aa', 2.1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into decimal_test_zerofill VALUES('aa', 2.45341413);
Query OK, 1 row affected, 1 warning (0.00 sec)

-- 当插入有符号数据，会报错
mysql> insert into decimal_test_zerofill VALUES('aa', -2.45341413);
ERROR 1264 (22003): Out of range value for column 'score' at row 1

mysql> select * from decimal_test_zerofill;
+------+-------------+
| name | score       |
+------+-------------+
| aa   | 000002.0000 |
| aa   | 000002.0000 |
| aa   | 000002.1000 |
| aa   | 000002.4534 |
+------+-------------+
4 rows in set (0.00 sec)

mysql>

特别说明:

Navicat 这里同样存在不填充 0 的查询结果，这只是navicat 本身的问题；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JsIMjyOQ-1608369891925)(img/image-20201218012442360.png)]

字符串

char & varchar & text 区别：

(1) 数据的检索效率是：char>varchar>text

(2) 保存字符串 “world”:

CHAR(60)： 5个字母，补齐55个空格，实际占用60

VARCHAR(60)：5个字母，实际占用5+1

TEXT(60)：5个字母，实际占用5+2

时间

-- 创建表
mysql> CREATE TABLE `date_test` (
    ->   `date` date DEFAULT NULL,
    ->   `date_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    ->   `time_stamp` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    ->   `time` time DEFAULT NULL,
    ->   `year` year(4) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into date_test(`date`,`date_time`,`time_stamp`,`time`,`year`) VALUES('2020-01-01 20:02', '2020-01-01 20:02', '2020-01-01 20:02', '2020-01-01 20:02', '2020');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> select * from date_test;
+------------+---------------------+---------------------+----------+------+
| date       | date_time           | time_stamp          | time     | year |
+------------+---------------------+---------------------+----------+------+
| 2020-01-01 | 2020-01-01 20:02:00 | 2020-01-01 20:02:00 | 20:02:00 | 2020 |
+------------+---------------------+---------------------+----------+------+
1 row in set (0.00 sec)

mysql>

datetime & timestamp 的区别：

（1）两者的存储方式不一样

TIMESTAMP - 把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储；

查询时，将其又转化为客户端当前时区进行返回。

DATETIME - 不做任何改变，原样输入和输出

基于上面的数据进行验证：

 mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set, 1 warning (0.00 sec)

mysql> set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from date_test;
+------------+---------------------+---------------------+----------+------+
| date       | date_time           | time_stamp          | time     | year |
+------------+---------------------+---------------------+----------+------+
| 2020-01-01 | 2020-01-01 20:02:00 | 2020-01-01 12:02:00 | 20:02:00 | 2020 |
+------------+---------------------+---------------------+----------+------+
1 row in set (0.00 sec)

mysql>

(2) 两者所能存储的时间范围不一样

timestamp所能存储的时间范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’

datetime所能存储的时间范围为：‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’

小小瓶

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
MYSQL数据类型

MYSQL数据类型综述数值整型类型大小范围（有符号）范围（无符号）TINYINT1字节(-27，27-1)(0，2^8-1)SMALLINT2 字节(-215，215-1)(0，2^16-1)MEDIUMINT3 字节(-223，223-1)(0，2^24-1)INT或INTEGER4 字节(-231，231-1)(0，2^32-1)BIGINT8 字节(-263，263-1)(0，2^64-1)在创建
复制链接

扫一扫