mysql使用-常见函数

准备

表结构和数据

 sql脚本

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for temp
-- ----------------------------
DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `data` json NULL,
  `create_date` datetime NULL DEFAULT NULL,
  `update_date` datetime NULL DEFAULT NULL,
  `create_by` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `update_by` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of temp
-- ----------------------------
INSERT INTO `temp` VALUES (1, '红红', '{\"id\": \"1232\", \"age\": \"12\", \"sex\": \"女\", \"name\": \"红红\"}', '2022-10-15 21:23:17', '2022-10-15 21:23:17', 'admin', 'admin');
INSERT INTO `temp` VALUES (2, '明明', '{\"id\": \"1232\", \"age\": \"12\", \"sex\": \"女\", \"name\": \"明明\"}', '2022-10-15 21:23:17', '2022-10-15 21:23:17', 'admin', 'admin');
INSERT INTO `temp` VALUES (3, '小小', '{\"id\": \"1232\", \"age\": \"12\", \"sex\": \"女\", \"name\": \"红红\", \"address\": {\"room\": \"120号\", \"strret\": \"阳光街大道\"}}', '2022-10-15 22:03:15', '2022-10-15 22:03:19', 'admin', 'admin');

SET FOREIGN_KEY_CHECKS = 1;

1、时间转字符串

-- 日期转字符串
SELECT DATE_FORMAT(a.create_date,'%Y-%m-%d'),DATE_FORMAT(a.create_date,'%Y-%m-%d %H:%i:%s'),a.create_date from temp a where a.id=1;

2、字符串转时间

-- 字符串转时间
SELECT STR_TO_DATE('2022-10-15 21:23:17','%Y-%m-%d'),STR_TO_DATE('2022-10-15 21:23:17','%Y-%m-%d %H:%i:%s');

3、从数据库json类型中获取值

从json类型的字段获取名为name的值

SELECT 
data->'$.name',
JSON_EXTRACT(data, '$.name'),
JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')),a.id 
FROM temp a ;

说明：使用data->'$.name'、JSON_EXTRACT(data, '$.name')这两种方式，结果会带上""双引号，可以使用JSON_UNQUOTE()去除

 获取json中对的key

select JSON_KEYS(data),JSON_KEYS(data->'$.address') FROM temp;

 说明：想获取data字段中adress下面的所有key，可以通过JSON_KEYS(data->'$.address')方式获取

4、获取当前年月日

select CURDATE();

5、获取当前年月日时分秒

select NOW();

5、时间操作

-- 获取指定日期是星期几，1对应星期天

SELECT DAYOFWEEK('2022-10-09') '星期天',DAYOFWEEK('2022-10-10') '星期一',DAYOFWEEK('2022-10-11') '星期二',DAYOFWEEK('2022-10-12')'星期三',DAYOFWEEK('2022-10-13')'星期四',DAYOFWEEK('2022-10-14')'星期五',DAYOFWEEK('2022-10-15')'星期六';

 -- 获取两个日期相隔天数，跟日期后的时分秒无关

SELECT DATEDIFF('2022-10-14','2022-10-12');

-- 获取两个日期相隔时间，跟日期后的时分秒有关

HOUR:相隔小时数

MINUTE:相隔分钟数

SECOND:相隔秒数

select TIMESTAMPDIFF(MINUTE,'2022-10-12 10:20:59','2022-10-12 10:21:56');

 -- 具体日期加上时间段

2000-12-31 23:59:59+100s

2000-12-31 23:59:59+2分钟

2000-12-31 23:59:59+2小时

1997-12-31 23:59:59+4分钟100秒

1997-12-31 23:59:59-4分钟100秒

SELECT '2000-12-31 23:59:59' + INTERVAL 100 SECOND;
SELECT '2000-12-31 23:59:59' + INTERVAL 2 MINUTE;
SELECT '2000-12-31 23:59:59' + INTERVAL 2 HOUR;

SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL '4:100' MINUTE_SECOND);
SELECT DATE_SUB('1997-12-31 23:59:59', INTERVAL '4:100' MINUTE_SECOND);

6、字符串操作

拼接

-- 拼接
select * from temp a where a.name like concat('%','红','%');

7、流程控制

-- 如果为null，取第二个参数的值

SELECT IFNULL(a.create_date,a.update_date) from temp  a;

8、插入或新增时自动更新创建时间或修改时间

时间类型为timestamp

#修改表 temp 中的 create_time 列 在插入新的数据时 如果值为空就设置为当前的系统时间
ALTER TABLE temp MODIFY create_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';

#修改表 temp 中的 update_time 列 在修改的数据时 如果值为空就设置为当前的系统时间
ALTER TABLE temp MODIFY update_time timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间';

时间类型为datetime

#修改表 temp 中的 create_time 列 在插入新的数据时 如果值为空就设置为当前的系统时间
ALTER TABLE temp MODIFY create_date datetime NOT NULL DEFAULT NOW() COMMENT '创建时间';

#修改表 temp 中的 update_time 列 在修改的数据时 如果值为空就设置为当前的系统时间
ALTER TABLE temp MODIFY update_time datetime not null default NOW()  on update NOW()  COMMENT '更新时间';