MYSQL数据类型

目录

一、数值数据类型

1.常用数值数据类型

2.整数数据长度问题

3.Mysql使用TINYINT(1)字段表示Boolean类型问题

4.浮点类型-近似值- Floating-Point Types

5.定点类型-精确值（Fixed-Point Types）

二、日期数据类型

1.自动更新日期

2.MySQL-TIMESTAMP时间存储问题

3.分隔符转换和超限问题

4.时间转换

5.两位数年份问题

三、字符串数据类型

1.常用字符串类型

2.char和varchar类型区别

3.字符串比较问题

4.大字段数据类型-BLOB和TEXT

5.枚举类型

四、其他类型

1.空间数据类型

2.Json数据类型

---

一、数值数据类型

1.常用数值数据类型

数据类型有 tinyint ，smallint ，mediumint ， int ，bigint

1 bytes = 8 bit , 在计算机中，数值一律用补码表示和存储的，正数的补码与原码相同，负数的补码为“其反码+1”，**补码的第一位为符号位**。第一位为符号位的话，最大为2的7次方 -1，十进制数为127， 在计算机中用01111111表示，负数用-128表示-0，补码为10000000，为-2的7次方

因此1 bytes的大小有符号为-128到127 ，无符号位(0,255)

bit(M)：一个比特值的类型。M为位数，取值范围为1 ~ 64。如果省略M，则默认为1，可存放十进制的二进制位

TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
FLOAT	4 bytes	(-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)	单精度 浮点数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
DOUBLE	8 bytes	(-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的值	小数值

2.整数数据长度问题

mysql数据类型中的定义的长度不是实际的长度，INT（M）,M指示最大显示宽度，而是代表宽度，几乎没有实际意义，像int(10)和int(1)，表示的范围一样，无论 int(n)中的n取值多少，占用空间都为4 bytes，只在特殊情况下才有作用，在加上zerofill字段时，可用用0来填充不足的宽度；

比如：定义一个字段

test int(10) unsigned zerofill NULL

会自动在不足宽度情况下进行填充0操作，加zerofill 时，mysql会默认加上unsigned 字段，对于业务读取/写入数据库操作不受影响

3.Mysql使用TINYINT(1)字段表示Boolean类型问题

使用mybatis查询tinyint(1)的值是Boolean类型，mybatis逆向工程也会对这个字段类型在xml中生成为bit，实体类Entity中为Boolean，mysql中没有Boolean，所以它用tinyint(1)代替Boolean

mybatis逆向工程	tinyint(1) 字段	tinyint(4)字段
*.java	Boolean	Byte
*Mapper.xml	BIT	TINYINT

查询出的0表示false,非0表示为true

原因： TINYINT(1) 只是在显示的时候作为一个位进行输出 解决：

1）设置成TINYINT(4)则可以正常查询出值。

2）jdbcUrl添加参数：tinyInt1isBit=false（默认为true）

4.浮点类型-近似值- Floating-Point Types

FLOAT和DOUBLE类型表示近似的数值数据值。MySQL使用4个字节表示单精度值，8个字节表示双精度值

• float单精度小数部分只能精确到后面6位，加上小数点前的一位，即有效数字为7位

• double双精度小数部分能精确到小数点后的17位，加上小数点前的一位 有效位数为18位。

• float存在整数部分的话，整体有效数字为6位，doublet则为17位，超出部分会四舍五入

float(m)和float一样 ，长度m没有实际意义，m<=24(超过24会变为double类型)， 都是默认float类型，都只能存6个有效数字（只有小数的话，整数位0不算），整数部分超过6位，第6位会被四舍五入，多出位填充0；小数位超出则只需要四舍五入，以下为float值显示

原值	插入后
123456	123456
1234567	1234570
12345678	12345700
123456.78	123457
12.34567	12.3457
0.123456	0.123456
0.1234567	0.123457

Mysql也支持非标准语法，像float(m,n)：代表显示的值不会超过 m 位数字，小数点后面带有 n 位数字 、double也是同理。整数部分超出m位数会报错，小数部分会进行四舍五入

注意：为了获得最大的可移植性，要求存储近似数字数据值的代码应该使用FLOAT或DOUBLE PRECISION，不指定精度或位数

优点: float单精度在一些处理器上比double双精度更快而且只占用double双精度一半的空间
缺点: 但是当值很大或很小的时候，它将变得不精确

5.定点类型-精确值（Fixed-Point Types）

MySQL DECIMAL和NUMERIC数据类型用于在数据库中存储精确的数值。我们经常将DECIMAL数据类型用于保留准确精确度的列，例如会计系统中的货币数据，在MySQL中，NUMERIC被实现为DECIMAL，因此下面关于DECIMAL的注释同样适用于NUMERIC

column_name decimal(P,D);

P是表示有效数字数的精度。P范围为1〜65，D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P，decimal(P，D)表示列可以存储D位小数的P位数。整数部分超出m位数会报错，小数部分会进行四舍五入

column_name decimal(4,2)：可存储 -99.99~99.99 之间的数值

原值	插入后
12.12	12.12
12.123	12.12
12.128	12.13
123.12	Out of range value

mysql中decimal(M)等价于decimal(M，0)，不设置时默认为10，不包含小数部分

二、日期数据类型

Mysql表示时间值的日期和时间数据类型是date、time、DATETIME、TIMESTAMP和YEAR。

日期时间类型	占用空间	日期格式	最小值	最大值	零值表示
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	19700101080001	2038 年的某个时刻	00000000000000
DATE	4 bytes	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
TIME	3 bytes	HH:MM:SS	-838:59:59	838:59:59	00:00:00
YEAR	1 bytes	YYYY	1901	2155	0000

1.自动更新日期

timestamp 和datetime：默认值设置 CURRENT_TIMESTAMP，新增操作时默认使用当前时间，设置CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，修改操作时会自动更新当前时间，不需要代码进行维护

2.MySQL-TIMESTAMP时间存储问题

MySQL将TIMESTAMP值根据当前时区转换为UTC世界标准时间）用于存储，检索时根据UTC世界标准时间）和当前时区转换。默认情况下，每个连接的当前时区是服务器的时间。时区不变，存储的值也就不会发生变化，而对于DATETIME，不做任何改变，基本上是原样输入和输出。插入到DATETIME或TIMESTAMP列的值中的任何小数部分都将被存储而不是丢弃

3.分隔符转换和超限问题

MySQL允许字符串的“宽松”格式，其中任何标点字符都可以用作日期部分或时间部分之间的分隔符。

2021,4.28'10:40`23 -> 2021-04-28 10:40:23

在某些情况下，这种语法可能具有欺骗性。例如，'10:11:12'这样的值可能看起来像一个时间值，因为:，但如果在日期上下文中使用，则被解释为年份'2010-11-12'。因为“45”不是一个有效的月份，所以值“10:45:15”被转换为“0000-00-00

在“时间”范围之外但在其他方面有效的值将被剪辑到该范围的最近端点。例如，'-850:00:00'和'850:00:00'被转换为'-838:59:59'和'838:59:59'。无效的TIME值被转换为'00:00:00'。若采用了严格模式则会直接报错

4.时间转换

date：转换为datatime或 TIMESTAMP值会添加一个时间部分，没有秒数部分则为 00:00:00，转换为time没有用，会变为无效值 00:00:00

datatime和TIMESTAMP：转换为date会舍弃小数部分，只保留日期，转换为time会舍弃日期部分，只保留小数部分

5.两位数年份问题

年份是四位，当只有两位时，会存储为不同年份，像year、date、datetime等，如下所示：

00-69的年份值变成了2000-2069

70-99的年份值变成了1970-1999

三、字符串数据类型

1.常用字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

65535 为每行存储的最大字节数

2.char和varchar类型区别

存储空间：char(n)需要固定的存储空间，即当存入的字符长度小于n时，所需空间仍然是n个字符所需的存储空间；varchar(n)的所需存储空间是随字符长度而变化的，因为varchar类型存储了当前字符的长度

数据长度：数据长度小于255时，数据库采用1个字节记录varchar数据长度，当数据长度>255时，需要用两个字节存储长度，varchar不需要填充空格。char类型字段的最大长度是255，且255个字节可全部用于存储数据，存储长度小于n，会用空格在右边补足，检索时又会去除空格，如果没有采取严格模式的话，超出的字符会被截断

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	' '	4 bytes	''	1 byte
'ab'	'ab '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

最后一行若没有采取严格模式，则会直接截断

对于插入填充空格和检索去除空格的类型，建立唯一索引后，如果插入的前缀字符串一样，不管加没加空格，会造成唯一索引冲突，比如 test_column char(4) ,具有唯一索引，在已有‘123’的情况下插入‘123 ’则会报错

3.字符串比较问题

当操作符与不同类型的操作数一起使用时，会进行类型转换以使操作数兼容。有些转换是隐式发生的。例如，MySQL会根据需要自动将字符串转换为数字，反之亦然，比较有如下规则：

• 如果比较操作中的两个参数都是字符串/整数，则将它们作为字符串/整数进行比较
• 如果其中一个参数是TIMESTAMP或DATETIME列，而另一个参数是常量，则在执行比较之前将常量转换为时间戳
• 字符串和数字进行比较，是将字符串转换为数字进行比较的，具体是字符串从开头开始，截止到第一个不为字符的地方，将之转换为数字
• 字符串和数字进行比较，无法使用到该列的索引，因为有许多只要包含该数字的字符串都能查询到
• 字符串表示的数字很大时，转换为对应的整数或浮点数表达不了时，会造成精度丢失，进行四舍五入操作，mysql会对其进行近似比较，只要在一定区间范围内的数值都会相等

sql语句	结果
SELECT '0'=0	1
SELECT '1'=0	0
SELECT 'abc'=0	1
SELECT '01abc'=1	1
SELECT 1+'121a'	122
SELECT 1+'abc'	1
SELECT '9223372036854775807' = 9223372036854775807	1
SELECT '9223372036854775807' = 9223372036854775806	1
SELECT CAST('9223372036854775807' AS UNSIGNED) = 9223372036854775806	0

解决办法：

• 不要将字符串和数字进行比较，应尽量避免不同类型直接进行查询比较
• 使用CAST()函数将字段转换为同一个类型进行比较：Cast(字段名 as 转换的类型 )。

4.大字段数据类型-BLOB和TEXT

BLOB值被视为二进制字符串(字节字符串)，像照片，TEXT值被视为非二进制字符串(字符串)，不足时不会进行填充操作。每个BLOB或TEXT值在内部由一个单独分配的对象表示，不需要指定长度大小，加上长度没有意义。InnoDB将长度大于或等于768字节的固定长度字段编码为可变长度字段，可变长度字段可以在页外存储

Mysql数据库对于BLOB/TEXT这样类型的数据结构只能索引前N个字符。所以这样的数据类型不能作为主键，也不能是UNIQUE的

当为BLOB或TEXT列建立索引时，必须指定索引的前缀长度

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

（2）不会进行空格填充

(3)BLOB和TEXT列不能有默认值

这是有严格模式限制的，若果需要默认值，需要更改模式

（4）大字段性能问题

https://www.cnblogs.com/chenpingzhao/p/6719258.html

检索效率：char>varchar>text

 对于blob，text，varchar(5120)这样的大字段，innodb只会存放前768字节在数据页中，而剩余的数据则会存储在溢出段中（发生溢出情况的时候适用），最大768字节的作用是便于创建前缀索引/prefix index，其余更多的内容存储在额外的page里，哪怕只是多了一个字节。因此，所有列长度越短越好。

大字段在InnoDB里可能浪费大量空间。例如，若存储字段值只是比行的要求多了一个字节，也会使用整个页面来存储剩下的字节，浪费了页面的大部分空间。mysql在操作数据的时候，以page为单位，不管是更新，插入，删除一行数据，都需要将那行数据所在的page读到内存中，如果一个page中能够相对的存放足够多的行，那么命中率就会相对高一些，性能就会有提升。

MySQL 5.1 中的 innodb_plugin 引入了新的文件格式：`Barracuda (梭子鱼)`，该文件格式拥有新的两种行格式：compressed和dynamic，将大字段完全存放在溢出段中，数据段中只存放20个字节，TEXT/BLOB列 <=40 字节时总是存放于数据页.

innodb的page大小默认为16kb，innodb存储引擎表为索引组织表，树底层的叶子节点为**一个双向链表**，因此每个页中至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8k，但事实上应该更小，因为还有一些InnoDB内部数据结构要存储

5.枚举类型

ENUM是一个字符串对象，指定为输入值的字符串将自动编码为数字；可读查询和输出。这些数字被转换回查询结果中的相应字符串，最大可容纳65535个枚举值

CREATE TABLE shirts (name VARCHAR(40), size ENUM('x-small', 'small', 'medium') );

如：向shirts表中插入值为‘medium’的100万行需要100万字节的存储，而如果将实际的字符串‘medium’存储在VARCHAR列中则需要600万字节，原因在于枚举类型存储的实际是数字

枚举对象的索引值：enum类型中，0代表空字符串值，1代表第一个枚举值，顺序往下对应

Value	Index
NULL	NULL
''	0
'x-small'	1
'small'	2
'medium'	3

排序时也是根据索引值来排序的，不是根据枚举值。可以使用ORDER BY CAST(col AS CHAR)或者 ORDER BY CONCAT(col)来避免使用索引值排序

四、其他类型

1.空间数据类型

2.Json数据类型