MySQL DataType--数值类型

=========================================================
MySQL常见的整数类型有：
TINYINT: 占用8位空间
SMALLINT: 占用16位空间
MEDIUMINT: 占用24位空间
INT: 占用32位空间
BIGINT:只用64位空间

MySQL常见的实数类型有：
DECIAML: 用于存储精确的数据
FLOAT和DOUBLE：用于存储浮点类型的数据


可以设置数据类型的UNSIGNED属性来表示不允许有负值数据，同时增大正数的上限值。
如果TINYINT的存储范围为-128到127之间，而UNSIGNED TINYINT的存储范围为0到255。

=========================================================

在MySQL 4.1版本前使用浮点运算来实现DECIAML计算，因此会导致进度损失导致一些奇怪的结果。

在MySQL 5.0极其更高版本中，MySQL使用二进制字符串来保存DECIMAL类型数据（每4个字节存放9个数字），如对于DECIMAL(18,9)，小数部分存放9个数字需要4个字节，整数部分存放9个数字需要4个字节，小数点本身需要1个字节，因此DECIMAL(18,9)类型的数据需要9个字节的存放空间。

FLOAT使用4个字节存储，DOUBLE使用8个字节存储，MySQL使用DOUBLE作为内部浮点计算的类型。

CPU直接支持原生的浮点计算，因此浮点运算比DECIMAL运算更快。

由于DECIMAL类型需要额外的空间和计算开销，因此可以考虑将DECIMAL转换成BIGINT来计算，如对于仅需到万分之一的财务数据，可以将数据乘以100万后变成BIGINT数据来存放。

由于十进制的数据无法用二进制来精确表示，只能尽可能的接近，如使用float(15,5)来存放1234567.10，最终存储的数据为1234567.12500，因此一定不能使用浮点数来存放金钱相关的数据。

=========================================================
数据类型占用存储空间和存储范围

bigint
从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。
P.S. bigint已经有长度了，在mysql建表中的length，只是用于显示的位数


int
从 -2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) 的整型数据（所有数字）。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer。


smallint
从 -2^15 (-32,768) 到 2^15 – 1 (32,767) 的整型数据。存储大小为 2 个字节。

tinyint
从 0 到 255 的整型数据。存储大小为 1 字节。

 

=========================================================
INT(M)与zerofill搭配使用，表示当现实的数据不足M位时，在前面填0补齐，如类型INT(2)，数值1会显示为01。

INT(M) 在 integer 数据类型中，M 表示最大显示宽度。在 INT(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。和数字位数也无关系 INT(3)、INT(4)、INT(8) 在磁盘上都是占用 4 btyes 的存储空间。

zerofill的作用仅在显示检索结果的时候，左边用0补齐到display到指定的显示宽度，实际存储时不补0，在查询的过滤条件中忽略0和空格。
测试：

CREATE TABLE `tb005` (
  `id` int(2) unsigned zerofill NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tb005(id)VALUES(1),(11),(111);

SELECT * FROM tb005;
+-----+
| id  |
+-----+
|  01 |
|  11 |
| 111 |
+-----+

 

=========================================================
判断字符串是否是数值

## 字符串以-或数字开头
## 字符串以数字结尾
## 字符串中只能包含0或1个小数点
## 字符串中其他部分只能包含数字

SELECT 
C1,
CASE WHEN
(C1 REGEXP '(^[0-9]|^-[0-9])([0-9]*)([.]?)([0-9]*)([0-9]$)')=1
THEN 'NUMBER' ELSE 'STRING' END AS C1_TYPE
FROM (
SELECT '' AS C1
UNION
SELECT '.'
UNION
SELECT '123.' 
UNION
SELECT '.123'
UNION
SELECT '123A'
UNION
SELECT '1-23'
UNION
SELECT '0..23'
UNION
SELECT '0'
UNION
SELECT '123'
UNION
SELECT '0.23'
UNION
SELECT '.'
) AS T1

 

=========================================================

字符串转换为数值

可以使用CAST将字符串转换为整数(SIGNED 和UNSIGNED)或小数(DECIMAL)，但不能将字符串直接转换为INT/BIGINT/FLOAT/DOUBLE等类型

在转换过程中，会尝试字符串最左边的数字，而不会报错，如'123.4ABC56'会被转换为123.4。

 

mysql> SELECT CAST('123.4ABC56' AS DECIMAL(18,5));
+-------------------------------------+
| CAST('123.4ABC56' AS DECIMAL(18,5)) |
+-------------------------------------+
|                           123.40000 |
+-------------------------------------+


mysql> SELECT CAST('123.4ABC56' AS SIGNED);
+------------------------------+
| CAST('123.4ABC56' AS SIGNED) |
+------------------------------+
|                          123 |
+------------------------------+

 

 

=========================================================