小结与选择建议

  • 在定义数据类型时，如果确定是 整数 ，就用INT ； 如果是 小数 ，一定用定点数类型DECIMAL(M,D)； 如果是日期与时间，就用DATETIME。
  • 任何字段如果为非负数，必须是 UNSIGNED
  • 【 强制 】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。

说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得
到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并
分开存储。

  • 【 强制 】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
  • 【 强制 】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大

于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

image.png

整数介绍

image.png
可选属性
M表示显示宽度，M的取值范围是(0, 255)。
UNSIGNEDint类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)。
ZEROFILL0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指
定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。

浮点类型

image.png
注意：浮点类型可能会造成精度缺失

定点数类型

  • MySQL中的定点数类型只有 DECIMAL 一种类型。
  • 使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65，0<=D<=30，D<M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。
  • 当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

位类型：BIT

image.png

日期与时间类型

image.png

YEAR类型
INSERT INTO test_year VALUES('2020','2021');

DATE类型
DATE类型表示日期，没有时间部分，格式为 YYYY-MM-DD
使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期。
INSERT INTO test_date1 VALUES ('2020-10-01'), ('20201001'),(20201001);
INSERT INTO test_date1 VALUES ('2020-10-01'), ('20201001'),(20201001);

TIME类型
TIME类型用来表示时间，不包含日期部分。

  • 可以使用不带有冒号的字符串或者数字，格式为’ HHMMSS ‘或者 HHMMSS 。如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不是12:10:00。
  • 使用 CURRENT_TIME() 或者 NOW() ，会插入当前系统的时间。
  • 如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。

INSERT INTO test_time1 VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
INSERT INTO test_time1 VALUES ('123520'), (124011),(1210);

DATETIME类型

  • 以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
  • 以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式
  • 使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向DATETIME类型的字段插入系统的当前日期和时间。

INSERT INTO test_datetime1 VALUES ('2021-01-01 06:50:30'), ('20210101065030');

TIMESTAMP类型
存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

用得最多的日期时间类型，就是 DATETIME 。虽然 MySQL 也支持 YEAR（年）、 TIME（时间）、DATE（日期），以及 TIMESTAMP 类型，但是在实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便
一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用 时间戳 ，因为DATETIME虽然直观，但不便于计算。

文本字符串类型

image.png
char与varchar
image.png

  • 情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的，结果得不偿失。
  • 情况2：固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。
  • 情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

text类型
image.png
TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来代替。还有TEXT类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表。

ENUM类型

ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。

  1. CREATE TABLE test_enum(
  2. season ENUM('春','夏','秋','冬','unknow')
  3. );
  4. INSERT INTO test_enum
  5. VALUES('春'),('秋');
  7. # 忽略大小写
  8. INSERT INTO test_enum
  9. VALUES('UNKNOW');
  11. # 允许按照角标的方式获取指定索引位置的枚举值
  12. INSERT INTO test_enum
  13. VALUES('1'),(3);
  15. # Data truncated for column 'season' at row 1
  16. INSERT INTO test_enum
  17. VALUES('ab');
  19. # 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
  20. INSERT INTO test_enum
  21. VALUES(NULL);

SET类型

SET表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为 64 。设置字段值时，可以取取值范围内的 0 个或多个值。

二进制字符串类型

MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据。
BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。

JSON类型

INSERT INTO test_json (js) 
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", 
"city":"beijing"}}');

当需要检索JSON类型的字段中数据的某个具体值时，可以使用“->”和“->>”符号。
SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province' 
AS province, js -> '$.address.city' AS city
   -> FROM test_json;

空间类型

MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东
西，可以是一个实体，例如一座山；可以是空间，例如一座办公楼；也可以是一个可定义的位置，例如
一个十字路口等等。