MySQL数据库操作补坑(一)数据类型

疫情期间数据库学的一直不是很好，后边发现别人都比自己会的多，现在来补补坑。

---

数据类型

数据类型介绍

1）、整数类型

整数类型	字节数	无符合的取值范围	有符合的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT	4	0~4294967295	-2147483648~2147483647
INTEGER	4	0~4294967295	-2147483648~2147483647
BIGNT	8	0~18446744071709551615	-9223372036854775808~9223372036854775807

基本形式：数据类型(显示宽度)

计算无符合数的取值范围

• 取值范围
  一个字节 = 8bit。由此可推算出，TINYINT类型数据无符号的取值范围为 2^8-1= 255；有符号的取值范围2^7-1=127。
• zerofill
  搭配zerofill使用参数使用时，数字不足的显示空间由0填补。使用该参数时，MYSQL会自动加上UNSIGNED属性，只能表示无符号数，其显示宽度比默认宽度小1。
• 显示宽度
  设置为int(11)的数据当输入111111时也可以显示，此时设置的显示宽度在显示该记录时将失效，但数据的显示宽度不能大于数据的默认宽度。如果大于了其最大值，将是不可插入的。

2）、浮点类型和定点数类型

整数类型	字节数	负数的取值范围	非负数的取值范围
FLOAT	4	-3.4028223466E+38 ~ -1.175494351E-38	0和1.175494351E-38 ~ 3.402823466E+38
DOUBLE	8	-1.7976931348623157E+308~ -2.2250738585072014E-308	0和2.2250738585072014E ~ 1.7976931348623157E+308
DECIMAL	M+2	同DOUBLE类型	同DOUBLE类型

基本形式：数据类型(M,D)

M参数为精度，小数点不占位置
D参数称为标度，是指小数点后的长度为D

• 精度问题
  在定义时不建议此形式，其不是标准用法，可能影响数据库的迁移
  如果插入值的的精度高于实际定义的精度，系统会自动进行四舍五入处理。FLOAT和DOUBLE类型在四舍五入时不会报错，而DECIMAL型会有警告。
  如果不指定精度，浮点数和定点数尤其默认的精度。FLOAT型和DOUBLE型默认保存实际精度(与操作系统有关)。DECIMAL型默认为整数。

3）、日期与时间型

整数类型	字节数	取值范围	0值表示
YEAR	1	YYYY	0000
DATE	4	YYYY-MM-DD	0000-00-00
TIME	3	HH:MM:SS	00:00:00
DATETIME	8	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	4	YYYY-MM-DD HH:MM:SS	00000000000000

插入的值如果超过了范围，系统就会报错，并插入零值，不同的日期与时间类型有不同的零值。

• YEAR类型
  输入格式为YYYY，例如2020、2021…当超出其范围时会插入0值
  用两位数表示：
  ‘00’ ~ '69’ 转换为2000~2069
  ‘70’ ~ ‘99’ 转换为1970~1999
  ‘35’ 转换为2035
  ‘90’ 转换为1990
  ‘0’ 和 '00’ 效果一样

• TIME
  输入格式：HH:MM:SS
  HH表示时，MM表示分，SS表示秒，取值范围为0~59
  TIME类型的取值范围可以从 ‘-838:59:59’ ~ ‘838:59:59’，小时的范围是 ‘0 ~ 23’。但是为了表示某种特殊需要的时间间隔，将TIME类型的范围扩大了。而且，还支持了负值。
  TIME类型字段的表示方式：'D HH:MM:SS'格式化的字符串表示。D表示天数，取值范围是 ‘0~34’ 保存时，小时值等于(D*24+HH)；也支持'HH:MM'/'D HH:MM'/'D HH'/'SS'等形式。345454的输入会装换为34:45:54。输入0和‘0’，那么TIME类型会转为0000:00:00。
  使用CURRENT_YIME或NOW()输入当前系统时间如果TIME超出范围，会截取为最接近的端点。
  TIme类型是专门用来存储时间数据的，而且只占了3个字节，如果只需要存储时间数据，选择TIME类型。

• DATA
  以YYYY-MM-DD的形式显示
  赋值方法：‘YYYY-MM-DD’或‘YYYYMMDD’格式的字符串。还支持一些不严格的语法格式，任何标点都可以作为间隔符：‘YYYY(/@.等)MM(/@.等)DD’

  ‘89@3@8’ 转换为 1989-03-08

  ‘00’~‘69’ 转换为 2000 ~ 2069
  ‘70’ ~ ‘99’ 转换为1970 ~ 1999
  DATE类型只占用4个字节，最适合用来存日期。

• DATETIME
  DATETIME类型使用8个字节来表示日期和时间。MySQL中以‘YYYY-MM-DD HH:MM:SS’表示
  同DATE类型一样，也可用标点作为间隔符，而且时间部分也可以用间隔符分开:：2020@10@25 080808。支持YYYYMMDDHHMMSS的形式。
  使用NOW()来输入当前系统日期和时间。
  DATETIME类型的作用等价于DATE类型和TIME类型的组合

• TIMESTAMP
  使用4个字节来表示日期和时间。TIMESTAMP类型范围比较小没有DATETIME类型的范围大。
  TIMESTAMP类型的几种与DATATIME类型的不同的形式
  1）使用CURRENT_TIMESTAMP来输入系统当前日期与实践。
  2）输入NULL时，系统会输入系统当前日期与时间。
  3）无任何输入时，系统会输入系统当前日期与时间。
  4）TIMESTAMP类型是按照时区来显示的。
  需要显示日期与时间，TIMESTAMP类型能够根据不同地区的时区来转换时间。但是，TIMESTAMP类型的范围太小。（max = 2038-01-19 11:14:07）。因此，若需要的时间范围比较大，还是选择DATETIME类型比较安全

4）、字符串类型

CHAR、VARCHAR、BOLD、TEXT、ENUM和SET。

• char类型与varchar类型
  基本形式：字符串类型(M)
  CHAR类型的长度是固定的，在创建表示就指定了。
  VARCHAR类型的长度是可变的，在创建表时指定了最大长度。VARCHAR(100)的最大长度是100。但是，不是每条记录都要占用100个字节，而是在这个最大范围内，使用多少分配多少。VARCHAR类型实际占用的空间位字符串的实际长度+1，可以有效节约空间。

char在存储的时候会将右侧空格进行剔除，保留左侧空格。
varchar在存储的时候保留所有空格，不进行任何删除
varchar和char在查询的时候都只会根据where条件中的左侧空格进行判断，右侧末尾的空格会忽略

• TEXT

类型	允许的长度	存储空间
TINYTEXT	0~255字节	值的长度+2个字节
TEXT	0~65535字节	值的长度+2个字节
MEDIUMTEXT	0~167772150字节	值的长度+3个字节
LONGTEXT	0~4294967295字节	值的长度+4个字节

各种TEXT类型区别仅在于允许的长度和存储的空间不同。根据需要选择即可。

• ENUM
  又称为枚举类型。在创建表时，ENUM类型的取值范围就以列表的形式决定了。
  基本形式：属性名 ENUM(‘值1’,‘值2’,…‘值n’)其中，属性名参数指字段的名称，这些值末尾的空格会被系统直接删除。
  ENUM类型的值只能取列表中的一个元素。其取值列表最多能有65535个值。ENUM类型的值只能取列表中的一个元素。MYSQL中存入的是列表中顺序排列的编号，而不是其值。
  当ENUM类型加上NOT NULL属性时，其默认值为列表的第一个元素。不加的话会允许插入NULL，并且默认值是NULL。
  当只能选取列表中的一个值，选择ENUM类型；如果需要选取列表中的多个值的组合，则需要选择SET类型。
• SET
  在创建表时，SET类型的取值范围就以列表的形式指定了。
  基本形式：属性名 SET(‘值1’，‘值2’，…‘值n’)。其中，属性名参数指字段的名称，这些值末尾的空格会被系统直接删除。
  SET类型的值可以去列表中的一个元素或者过个元素的组合。取多个元素时，不同元素之间用逗号分隔开。SET类型的值最多只能是有64个元素构成的组合。
  同ENUM类型一样，列表中的每一个值都有一个顺序排列的编号。MYSQL存入的是这个编号，而不是类表中的值。
  插入记录时，SET字段中的元素顺序无关紧要。存入MYSQL中会自动按照定义时的顺序显示。

  举例 : (‘A’，‘B’，‘C’，‘D’，‘E’)。 插入(‘C’，‘B’，‘D’)结果显示为(‘B’，‘C’，‘D’)

5）、二进制类型

类型	取值范围
BINARY(M)	字节数为M，允许长度为0~M的定长二进制字符串
VARBINARY(M)	允许长度为0~M的边长二进制字符串，字节数为值的长度加1
BIT(M)	M位二进制数据，M最大值为64
TINYBLOB	可变长二进制数据，最多255个字节
BLOB	可变长二进制数据，最多(216-1)个字节
MEDIUMBLOB	可变长二进制数据，最多(224-1个字节)
LONGBLOB	可变长二进制数据，最多(232-1个字节)

• BINARY和VARBINARY类型
• BINARY类型和VARCHARY类型都是在创建表时指定了最大长度，其基本形式如下：
  字符串类型(M)
  参数指定了数据类型为BINARY类型还是VARBINARY类型；M参数指定了该二进制数的最大字节长度为M。例如BINARY(10)就是制定BINARY数据类型为BINARY类型，最大长度为10。
  BINARY类型的长度是固定的，在创建表时就指定了。不足最大长度的空间由‘\0’补全。
  VARBINARY类型的长度是可变的，在创建表时指定了最大长度。指定好了VARBINARY类型的最大长度以后，其长度可以在0到最大长度之间。在这个最大长度的范围以内，使用多少就分配多少。该类形实际占用的空间为实际长度加1。这样可以有效地节约系统的空间。
• BIT
  创建时指定了最大长度，基本形式：BIT(M)
  其中M指定了该二进制数的最大字节长度为M，M的最大值为64。
  若该字段的类型BIT(4)，存储的数据是从0~15。因为变成二进制后，15的值为1111，长度为4。插入的值为16二进制为10000。长度为5，就超过了最大长度。
  在查询BIT类型的数据时，要用BIN(字段名+0)来将值转换为二进制显示。
• BLOB
  是一种特殊的二进制类型。BLOB可以用来保存数据量很大的二进制数据，包括图片。该类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。这几种BLOB类型最大的区别就是能够保存的最大长度不同。
  BLOB类型用来存储二进制数据，而TEXT类型是文本模式进行比较和排序的。

  BOLB类型主要用来存储图片，PDF文档等二进制文件。通常情况下将图片、PDF文档都可以存储在文件系统中，然后再数据库中存储这些文件的路径，该种方法简单，但是访问速度比在数据库中存储慢。

如何选择数据类型

1）、整数类型和浮点数类型

区别及选择

1. 整数类型不能表示小数。浮点类型可以表示小数。
2. 不同的整数类型取值范围不同。字段值不超过255，推荐TINYINT类型
3. DOUBLE类型的精度比FLOAT类型高。如果需要精确到小数点后十位以上，选择DOUBLE类型

2）、浮点数类型和定点数类型

区别及选择

1. 当插入精度高于实际定义的精度，系统自动四舍五入时，浮点型不会报警，定点型会报警。
2. 在未指定精度的情况下，浮点型和定点性都有其默认的精度。FLOAT类型和DOUBLE类型默认会保存实际精度（精度与操作系统和硬件的精度有关）DECIMAL型默认整数。
3. 在MYSQL中，定点数精度比浮点数要高。而且浮点数会出现误差。当对数据精度要求比较高时，选择定点数。

3）、CHAR类型和VARCHAR类型

区别及选择

1. CHAR类型长度固定，VARCHAR类型在给定的最大长度内使用多少分配多少，占用的空间比VARCHAR类型小。对于长度变化较大的字符串类型最好是选择VARCHAR类型。
2. CHAR类型的处理速度比VARCHAR类型快。对于长度变化不大和查询速度要求高的字符串类型，最好选择CHAR类型

4）、时间和日期类型

区别及选择

1. YEAR类型只会表示年份。TIME类型只表示时间。DATA类型只表示日期
2. 只需要记录日期和时间，选择DATATIME类型，和TIMESTAMP类型。当时间范围大时，选择DATATIME类型。当需要根据时区显示时，选择TIMESTAMP类型。

5）、ENUM类型和SET类型

区别及选择

1. ENUM类型最多可以有65535个成员，而SET类型最多只能包含64个成员。ENUM类型只能从成员中选择一个，SET类型可以选择多个。所以，对于多个值选择一个的，可以选择ENUM类型，对于可以选取多个值的字段，选择SET类型。

6）、TEXT和BOLB类型

区别及选择

1. TEXT类型只能存储字符数据。BOLD类型可以存储二进制数据。对于纯文本等数据，选择TEXT，对于图片等二进制的数据，选择BLOB类型。
2. TEXT类型包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。四者的最大区别是内容的长度不同。BLOB类型也是如此。

---

---

问题补充：

什么数据类型能够存储路径

CHAR、VARCHAR、TEXT等字符串类型都可以。当路径中使用 ‘\’ 符号时，这个符号会被过滤。解决的办法：路径中用 ‘/’ 或 ‘\’ 来代替 ‘\’

MYSQL中如何使用布尔型

为支持SQL标准中的BOOL和BOOLEAN类型，MYSQL也可以定义此类型。但是会被转换为TINYINT(1)。就是说MYSQL中布尔型等于TINYINT(1)。将一个字段定义为布尔值时，数据库中存的是TINYTINT(1)。

MYSQL中如何存储JPG图片和MP3音乐

一般情况下，数据库中不直接存储图片和音频。而是存储器路径。通过BLOB类型可以在MYSQL数据库中存储图片和音频文件（二进制）