1、获取当前日期
Date date=new Date();
SimpleDateFormat format=newSimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);//根据具体需求
format(date);
2、merge into不能用在mysql中
3、@DS注解用法
说到这个注解,首先要提到多数据源链接配置,因为单数据源时不会存在数据源找不到的情况。
pom.xml 引入依赖:
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.0</version>
</dependency>
yaml配置文件
# 多数据库连接配置
datasource:
dynamic:
#配置主数据库
primary:xx_db
datasource:
#主数据库配置
xx_db:
username: xx
password: xxx
url: jdbc:mysql://sourcename.mysql.xxx.xx.xx:3306/xxxx
driver-class-name: com.mysql.cj.jdbc.Driver
#其他数据库配置
mm_db:
username: xx
password: xxx
url: jdbc:mysql://sourcename.mysql.xxx.xx.xx:3306/xxxx
driver-class-name: com.mysql.cj.jdbc.Driver
因为这里配置了不止1个数据源,所以在使用时如果不标注,就会出现找错库表的情况,所以这里就需要@DS注解出场了。只要在service实现上或mapper上加上@DS(“xx_db”)注解,那对应的操作对象就是xx_db,不会存在歧义和报错。
4、mysql触发器
before/after insert/update/delete 6种组合
CREATE TRIGGER triggerName BEFORE INSERT ON xx_tableName FOR EACH ROW
begain
......;
......;
......;(操作SQL语句)
end
CREATE TRIGGER triggerName AFTERINSERT ON xx_tableName FOR EACH ROW
begain
......;
......;
......;(操作SQL语句)
end
CREATE TRIGGER triggerName BEFORE UPDATE ON xx_tableName FOR EACH ROW
begain
......;
......;
......;(操作SQL语句)
end
CREATE TRIGGER triggerName AFTER UPDATE ON xx_tableName FOR EACH ROW
begain
......;
......;
......;(操作SQL语句)
end
CREATE TRIGGER triggerName BEFORE DELETE ON xx_tableName FOR EACH ROW
begain
......;
......;
......;(操作SQL语句)
end
CREATE TRIGGER triggerName AFTER DELETE ON xx_tableName FOR EACH ROW
begain
......;
......;
......;(操作SQL语句)
end
MYSQL 触发器不支持本表操作,如:
CREATE TRIGGER 'createIdTrigger' after insert on xx_table for each row
begain
declare mode_name varchar(64);
set mode_name = (case new.source
when 1
then 'CCC'
when 2
then 'iii'
when 3
then 'rrr'
when 4
then 'ttt'
end);
set xx_table.id = next('ZOEY',mode_name,new.send_date);
不成功!!!
CREATE TRIGGER 'createIdTrigger' before insert on xx_table for each row
begain
declare mode_name varchar(64);
set mode_name = (case new.source
when 1
then 'CCC'
when 2
then 'iii'
when 3
then 'rrr'
when 4
then 'ttt'
end);
set new.id = next('ZOEY',mode_name,new.send_date);
成功!!!
总结:MYSQL中触发器中不能对本表进行 insert ,update ,delete操作,以免递归循环触发
5、mysql实现自定义sequence自增序列
//创建一个自增序列表
CREATE TABLE IF NOT EXISTS sequence (
sequence_name VARCHAR(50) PRIMARY KEY COMMENT '序列名称',
current_value INT NOT NULL COMMENT '序列当前值',
increment INT NOT NULL DEFAULT '1' COMMENT '序列步长',
sequence_date VARCHAR(10) NOT NULL COMMENT '日期'
) COMMENT '序列表';
//创建currval方法,用于获取当前序列值
DELIMITER $$
CREATE FUNCTION currval(seq_name VARCHAR(50))
RETURNS INTEGER
BEGIN
DECLARE cur_value INTEGER DEFAULT 0;
-- 查询是否存在当前序列
SELECT count(1) INTO cur_value FROM sequence
WHERE sequence_name = seq_name
AND sequence_date = DATE_FORMAT(SYSDATE(),'%Y%m%d');
IF cur_value <> 0 THEN
-- 存在则返回当前序列值
SELECT current_value INTO cur_value FROM sequence
WHERE sequence_name = seq_name
AND sequence_date = DATE_FORMAT(SYSDATE(),'%Y%m%d');
ELSE
-- 删除不是当天的序列
DELETE FROM sequence WHERE sequence_name = seq_name;
-- 不存在,则插入当前序列值
INSERT INTO sequence(sequence_name, current_value, increment, sequence_date)
VALUES(seq_name, 0, 1, DATE_FORMAT(SYSDATE(),'%Y%m%d'));
END IF;
RETURN cur_value;
END $$;
//创建nextval方法,用于获取下一个序列值
DELIMITER $$
CREATE FUNCTION nextval(seq_name VARCHAR(50))
RETURNS VARCHAR(32)
BEGIN
DECLARE curVal INTEGER;
-- 获取当前序列值
SET curVal = currval(seq_name);
-- 更新序列表
UPDATE sequence SET current_value = current_value + increment
WHERE sequence_name = seq_name
AND sequence_date = DATE_FORMAT(SYSDATE(),'%Y%m%d');
-- 返回序列,格式:xxxx+20210220+8位长度序列(不足左补0)
RETURN CONCAT(seq_name, DATE_FORMAT(SYSDATE(), '%Y%m%d'),LPAD(curVal,8,0));
END $$;