3.数据类型

数据类型

数值类型

分为精确数字类型和近似数据类型。

精确数字类型：TINYINT、SMALLINT、MEDIUMINT、INTEGER、BIGINT

近似数据类型：FLOAT、DOUBLE、DECIMAL

[!TIP]

1. 能用无符号就用无符号数字，既增加约束又增加有效范围。
2. 枚举状态或分类（例如，1-待处理，2-成功，3-失败）优先选用无符号 TINYINT，组合状态或分类（例如，11213141，表示11类下的21子类下的31子类下的41子类）按需使用无符号 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。
3. 年份数据优先选用SMALLINT。
4. 主键ID优先选用 BIGINT，避免自增ID用尽或者足够长以兼容分布式ID。
5. 财务数据优先选用 DECIMAL，或者使用BIGINT和固定倍率常量（分、毫、厘）将数据扩大为整数。

mysql tinyint

Mysql tinyint(1)与tinyint(4)的区别

 CREATE TABLE `pre_demo` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
   `unsigned_t` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
   `signed_t` tinyint(4) unsigned zerofill NOT NULL DEFAULT '0000',
   `t1` tinyint(1) unsigned zerofill NOT NULL DEFAULT '0',
   `t2` tinyint(2) unsigned zerofill NOT NULL DEFAULT '00',
   `t3` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
   `t4` tinyint(4) unsigned zerofill NOT NULL DEFAULT '0000',
   `t5` tinyint(5) unsigned zerofill NOT NULL DEFAULT '00000',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 INSERT INTO pre_demo VALUES(NULL,8,8,8,8,8,8,8);
 INSERT INTO pre_demo VALUES(NULL,123,123,123,123,123,123,123);
 
 tinyint占一个字节
 tinyint(1) 与 tinyint(4) 能够存储的数据范围是一样的，都是 0 到 255（无符号的）。区别在于，当使用 zerofill 时，查询结果显示的长度可能不同。
 zerofill 和 指定长度配合使用，可用于统一数据的显示长度，比如在数据库层面快速生成统一长度的流水号。
 一般情况下，无须刻意指定整型数据类型的长度。使用 MySQL 的默认长度tinyint(4) 即可。

日期时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

时间日期类型

DATE、TIME、DATETIME、TIMESTAMP、YEAR

[!TIP]

• DATE：存储日期数据（例如，生日）
• TIME：存储时间数据（例如，视频时长、工单处理耗时、任务执行时长），但不推荐使用该类型，而是使用整数+应用层格式化替代。
• YEAR：存储年份数据（例如，报表年份），但不推荐使用该类型，而是用 SMALLINT 代替。
• DATETIME：存储日期时间数据，不携带时区信息
• TIMESTAMP：存储时间戳数据，不携带时区信息，但会有时区转换，但不推荐使用该类型，而是使用 DATETIME + 应用层 UTC 格式化替代

各时间类型转换

DATE
DATETIME
TIMESTAMP

SELECT FROM_UNIXTIME(1447430881);
SELECT FROM_UNIXTIME(1447430881) + 0;
SELECT FROM_UNIXTIME(1447430881, '%Y %D %M %h:%i:%s %x');

NOW([fsp])

返回当前日期和时间，该值以会话时区表示。在不同计算上下文中以不同格式返回，如果给定fsp参数以指定从0到6的小数秒精度，则返回值包括该数字中的小数秒部分：

• 字符串：'YYYY-MM-DD hh:mm:ss'
• 浮点数：YYYYMMDDhhmmss

STR_TO_DATE(str,format)

DATE_FORMAT()函数的逆函数，参数为一个代表时间的字符串str，以及一个格式化字符串。该函数返回值取决于时间字符串str中包含的具体内容：

• 日期+时间：返回DATETIME
• 日期：DATE
• 时间：TIME
• 时间字符串str为NULL：返回NULL
• 无法解析：返回NULL并产生警告

服务器扫描时间字符串str，试图将格式与之匹配。格式字符串可以包含以%开头的文字字符和格式说明符。格式中的文字字符必须与字符串中的文字匹配。格式中的格式说明符必须与字符串的日期或时间部分匹配。

UNIX_TIMESTAMP([date])

• 无日期参数调用，返回UNIX时间戳，表示自“1970-01-01 00:00:00”UTC以来的秒数。
• 有日期参数调用，返回该日期到“1970-01-01 00:00:00”UTC以来的秒数。
  • 日期参数可以是date、DATETIME或TIMESTAMP字符串，也可以是YYMMDD、YYMMDDhhmmss、YYMMDD或YYYYMMDD-hmmss格式的数字。如果参数包含时间部分，则可以选择包含小数秒部分。如果没有给出参数或参数不包含小数秒部分，则返回值为整数；如果给出的参数包含小数秒的部分，则为DECIMAL。
  • 服务器将日期解释为会话时区中的值，并将其转换为UTC中的内部Unix时间戳值。
  • 当date参数是TIMESTAMP列时，UNIX_TIMESTAMP（）直接返回内部时间戳值，没有隐式的“字符串到UNIX时间戳”转换。
  • 参数值的有效范围与TIMESTAMP数据类型相同：对于32位平台，从“1970-01-01 00:00:01.000000”UTC到“2038-01-19 03:14:07.999999”UTC；对于在64位平台上运行的MySQL，UNIX_TIMESTAMP（）的参数值的有效范围为“1970-01-01 00:00:01.000000’UTC到“3001-01-19 03:14:07.999999’UTC（对应于3253677199.999999秒）。
  • 无论MySQL版本或平台架构如何，如果将超出范围的日期传递给UNIX_TIMESTAMP（），它都会返回0。如果date为NULL，则返回NULL。

SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');

字符串类型

CHAR、VARCHAR

TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

[!TIP]

• CHAR：适用于存储定长字符串（例如，MD5），或者长度相对均衡且需频繁查询的字符串（例如，国际手机区号）。
• VARCHAR：适用于存储变长字符串（例如，产品名称、邮箱），
• TINYTEXT：不使用该类型，使用 VARCHAR 代替。
• TEXT：存储短文章（例如，电子邮件正文、新闻正文）
• MEDIUMTEXT：存储长文章（例如，专业论文）
• LONGTEXT：存储书籍（）

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

二进制大对象(BLOBS BinaryLargeOBjects)

JSON

CREATE TABLE `dept` (
  `id` int(11) NOT NULL,
  `dept` varchar(255) DEFAULT NULL,
  `json_value` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into dept VALUES(1,'部门1','{"deptName": "部门1", "deptId": "1", "deptLeaderId": "3"}');
insert into dept VALUES(2,'部门2','{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}');
insert into dept VALUES(3,'部门3','{"deptName": "部门3", "deptId": "3", "deptLeaderId": "5"}');
insert into dept VALUES(4,'部门4','{"deptName": "部门4", "deptId": "4", "deptLeaderId": "5"}');
insert into dept VALUES(5,'部门5','{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}');

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and dept='部门3';

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';

返回指定字段

select id,json_extract(json_value,'$.deptName') as deptName from dept;

包含某个对象

• JSON_OBJECT将键值对转换为对象

select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))

SELECT JSON_KEYS(json_value) FROM dept 

https://blog.csdn.net/asd529735325/article/details/107205214

json字段上的索引

https://blog.csdn.net/BREATH57/article/details/129320951