03.数据类型
字符串类型
char类型：固定长度，储存时不足的会被填充空白，从磁盘获取数据时会将后面的空白删除后返回有效的数据
varchar类型：可变长度类型。设定长度100后在数据库开发工具（IDE）中通过函数 LENGTH 检测目标字符串长度时是检测带有字符集的长度，例如 111 的长度是3，但是 11中 的长度是5（如果使用了 utf-8 字符集），即 utf-8 字符集中一个汉字是3个字节。而该字段实际可以容纳的字符的数量是不区分是否中英文以及字符集的，即如果全部是中文也可以保存100个汉字，所以如果在 java 中检测长度不可超过 db 设定的长最大长度时就检测通常的字符个数即可，不需区分字符集。即通过下面代码检测字符串的长度不超过100：
public static String getLeft(String ori,Integer len){
if(len > ori.length()) len = ori.length();
if(len < 0) len = 0;
return ori.substring(0,len);
}
// 使用案例
if(getLeft("目标字符串",100) <= 100 ) return true;
JSON
概述
mysql5.7 开始支持 json 类型数据
创建与查询
穿透到json内属性
新增字段时，数据类型选择 json，就和选择使用其他基础类型一样，查询数据时可以直接使用 json 内的属性。下面 sql 中表 document 中字段 json_field 的数据类型是 json，其中有属性 pf_coded 和 specification。
select d.id,d.name,json_field -> '$.specification' from document as d where extra -> '$.pf_coded' = '0704-0001';
虚拟字段
通过下面脚本创建一个虚拟列，关联到 json 类型字段中指定的属性上
ALTER TABLE document Add COLUMN `v_pf_coded` varchar(100) GENERATED ALWAYS AS (json_unquote(json_extract(`json_field`, _utf8mb4'$.pf_coded'))) VIRTUAL NULL;
字段名称是 json_field，其中有属性 pf_coded，上面脚本的意思是创建虚拟字段 v_pf_coded 关联到 json 类型字段 json_filed 的属性 pf_coded 上。该虚拟字段只能读不能写，可以为该虚拟字段创建索引
create index idx_v_pc_coded on document(v_pf_coded);
日期时间类型
日期增减
详情
在原基础上增加一年，计量单位如下：
| type类型 |
|---|
| MICROSECOND |
| SECOND |
| MINUTE |
| HOUR |
| DAY |
| WEEK |
| MONTH |
| QUARTER |
| YEAR |
| SECOND_MICROSECOND |
| MINUTE_MICROSECOND |
| MINUTE_SECOND |
| HOUR_MICROSECOND |
| HOUR_SECOND |
| HOUR_MINUTE |
| DAY_MICROSECOND |
| DAY_SECOND |
| DAY_MINUTE |
| DAY_HOUR |
| YEAR_MONTH |
# 下面是语法
DATE_ADD(date,INTERVAL expr type)
# 下面是案例
select date_add(bill_date,INTERVAL 1 YEAR) as bill_date
from maint_main ORDER BY id DESC LIMIT 100;
日期比较
datediff 函数拿第一个参数减掉第二个参数，两个参数有无时间不影响，只计算两个日期相差的天数
# 获取相差的天数
select datediff(now(),'2023-12-07 10:04:50') as diff;
# 通过时间戳比较秒数，内部使用 DATE_FORMAT 统一时间格式
SELECT TIMESTAMPDIFF(SECOND,DATE_FORMAT('2025-10-13 12:00:00','%Y-%m-%d %H:%i:%S'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) AS aa;
时区与格式
查看时区：SHOW VARIABLES LIKE '%time_zone%'; JAVA中12小时制：SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); JAVA中24小时制：SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); SQL中12小时制：date_format(NOW(), '%h:%i:%S') SQL中24小时制：date_format(NOW(), '%H:%i:%S') # 区别在于h是否大写 年月日格式化：date_format(NOW(), '%Y-%m-%d')
仅日期，短线两位数：select DATE_FORMAT(NOW(),'%Y-%m-%d') as today 时间带毫秒：select DATE_FORMAT(NOW(),'%T:%f') as theTime; 时间精确到秒：select DATE_FORMAT(NOW(),'%T') theTime 单独一个秒钟：select DATE_FORMAT(NOW(),'%s') theTime 创建表字段时的默认日期时间：
-- 前面一个default是创建本行数据时DB自动填充创建时间
-- 后面的ON UPDATE 表示本行数据被修改时候也会使用当前时间覆盖本字段上的时间
-- 字段是 DATETIME 类型或者 TIMESTAMP 类型都可以设置默认日期时间
ALTER TABLE `contactcategory`
ADD COLUMN `UpdateTime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后一次修改时间' ;
ALTER TABLE `table_name`
MODIFY COLUMN `UpdateTime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间' ;
格式化日期
详情
SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');# Nov 26 2020 04:55 PM
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); # 2020-11-26
完整格式通配符如下表
| %a | 缩写星期名 |
|---|---|
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值(00-31) |
| %e | 月的天，数值(0-31) |
| %f | 微秒 |
| %H | 小时 (00-23) |
| %h | 小时 (01-12) |
| %I | 小时 (01-12) |
| %i | 分钟，数值(00-59) |
| %j | 年的天 (001-366) |
| %k | 小时 (0-23) |
| %l | 小时 (1-12) |
| %M | 月名 |
| %m | 月，数值(00-12) |
| %p | AM 或 PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒(00-59) |
| %s | 秒(00-59) |
| %T | 时间, 24-小时 (hh:mm:ss) |
| %U | 周 (00-53) 星期日是一周的第一天 |
| %u | 周 (00-53) 星期一是一周的第一天 |
| %V | 周 (01-53) 星期日是一周的第一天，与 %X 使用 |
| %v | 周 (01-53) 星期一是一周的第一天，与 %x 使用 |
| %W | 星期名 |
| %w | 周的天 （0=星期日, 6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
常用的格式如下
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'); # 2020-11-26 23:59:57,%H表示24小时计时方式
日期字段记录修改时间
看下面建表语句的字段 last_datetime 的设置表示每当行被修改时都会更新该字段，即保存修改记录的时间
-- auto-generated definition
CREATE TABLE bill_code (
iid INT AUTO_INCREMENT PRIMARY KEY,
sid VARCHAR(100) DEFAULT '' NULL COMMENT '方案名称',
date_pattern VARCHAR(50) DEFAULT 'yyMMdd' NULL COMMENT '日期格式',
incr_length INT DEFAULT 3 NULL COMMENT '自增数字的长度,默认3位',
day_count INT DEFAULT 0 NULL COMMENT '当日序号',
month_count INT DEFAULT 0 NULL COMMENT '当月序号',
year_count INT DEFAULT 0 NULL COMMENT '当年序号',
last_datetime DATETIME DEFAULT CURRENT_TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '最后一次修改时间'
) COMMENT '单据编号';
本月第一天
select date_add(curdate(), interval - day(curdate()) + 1 day);
本月最后一天
select last_day(curdate());
本月天数
select day(last_day(curdate()));
获取一个时间段内每一天的日期
# 获取一个时间段内的每一天的日期
SET @beginDate='2019-09-25';
SET @maxDate ='2019-10-05';
SELECT DATE(@tempDay),@tempDay:=DATE_ADD(@tempDay,INTERVAL 1 DAY)
FROM mysql.help_topic f #该表的行数决定了本方法最多可以返回多少行数据
LEFT JOIN (SELECT @tempDay:=@beginDate) b ON 1=1
WHERE @tempDay<=@maxDate
ORDER BY DATE(@tempDay) DESC;
数字类型
随机数：select rand() 一百以内的一位小数：select round(rand() * 100,1) as A 保留一位小数：select format(rand() * 10,2) as AA; 字符串转换为数值类型：select cast('111' as signed); 数值转换为字符串类型：select concat(123,'') as AA;
保留小数位数： 使用 format 保留两位有效小数的结果是：113.12
select format(113.1156,2);
但是使用 format 的缺点是超过1000的会自动添加千分位的逗号，那么可以使用 round() 做小数位数的保留，如下面代码
select round(123.567,2);# 结果是123.57
截取获得整数部分数据，不四舍五入
select FLOOR(123456.6789);
# 结果：123456
截取获得整数部分数据，无条件个位加1
select CEILING(123456.6789);
# 结果是123457
舍去多余小数位数
select TRUNCATE(123456.6789,2);
# 结果:123456.67