MySQL数据类型--------整数类型实战
发布时间:2020-07-12 09:57:07
来源:51CTO
阅读:1291
作者:asd1123509133
1. 背景
* MySQL支持SQL标准整数类型整数(或INT)和SMALLINT。作为标准的扩展,MySQL还支持整数类型TINYINT、MEDIUMINT和BIGINT
2.整数类型所需的存储和值范围类型占用空间最小值最大值
(字节)(signed/Unsigned)(signed/Unsigned)
TINYINT1-128127
0255
SMALLINT2-3276832767
065535
MEDIUMINT3-83886088388607
016777215
INT4-21474836482147483647
04294967295
BIGINT8-92233720368547758089223372036854775807
018446744073709551615
3. zerofill 属性遇到整数类型应用
* 创建zerofill测试表 zerofill_testmysql> CREATE TABLE zerofill_test(
-> `id` INT(6) ZEROFILL
-> )ENGINE=INNODB CHARSET=utf8mb4;
Query OK, 0 rows affected (0.08 sec)
* 插入数值长度小于6的值mysql> INSERT INTO zerofill_test SELECT 11111;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
* 插入数值长度大于6的值mysql> INSERT INTO zerofill_test SELECT 2222222;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
* 查看zerofill_test表所有的数据mysql> SELECT * FROM zerofill_test;
+---------+
| id |
+---------+
| 011111 |
| 2222222 |
+---------+
2 rows in set (0.01 sec)
* zerofill总结
* 当显示的值长度小于定义时INT定义的长度,高位补0显示
* 当显示的值长度大于定义时INT定义的长度,照常显示
* zerofill显示属性,值不做任何修改
4. Unsigned 类型溢出现象
* 创建unsigned测试表 unsigned_testmysql> CREATE TABLE unsigned_test(
-> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> data INT UNSIGNED
-> )ENGINE=INNODB CHARSET=utf8mb4;
Query OK, 0 rows affected (0.12 sec)
* 插入两行一大一小的值mysql> INSERT INTO unsigned_test SELECT null, 1;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO unsigned_test SELECT null, 2;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
* 查看表中插入的数据值mysql> SELECT * FROM unsigned_test;
+----+------+
| id | data |
+----+------+
| 1 | 1 |
| 2 | 2 |
+----+------+
2 rows in set (0.00 sec)
* 利用小数值对大数值做减运算mysql> SELECT data - (SELECT data FROM unsigned_test where id=2) FROM unsigned_test where id=1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`mytest`.`unsigned_test`.`data` - (select `mytest`.`unsigned_test`.`data` from `mytest`.`unsigned_test` where 1))'
5. 整数类型应用总结
* signed/Unsigned: 是否有符号
* ZEROFILL: 显示属性,值不做任何修改
* 推荐不要使用UNSIGNED, 范围本质上没有大的改变
* UNSIGNED可能会有溢出现象发生
* 自增INT 类型主键建议使用BIGINT
6. 总结
以需求驱动技术,技术本身没有优略之分,只有业务之分。