30 行
1.2 KiB
SQL
30 行
1.2 KiB
SQL
-- 创建骑手token表
|
||
CREATE TABLE IF NOT EXISTS `ls_rider_token` (
|
||
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`rider_id` int(11) NOT NULL COMMENT '骑手ID',
|
||
`token` varchar(255) NOT NULL COMMENT 'token值',
|
||
`expires_at` datetime NOT NULL COMMENT '过期时间',
|
||
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`device_info` varchar(500) DEFAULT NULL COMMENT '设备信息',
|
||
`ip_address` varchar(45) DEFAULT NULL COMMENT 'IP地址',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `token` (`token`),
|
||
KEY `rider_id` (`rider_id`),
|
||
KEY `expires_at` (`expires_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='骑手token表';
|
||
|
||
-- 清理过期token的存储过程
|
||
DELIMITER $$
|
||
CREATE PROCEDURE CleanExpiredRiderTokens()
|
||
BEGIN
|
||
DELETE FROM `ls_rider_token` WHERE `expires_at` < NOW();
|
||
END$$
|
||
DELIMITER ;
|
||
|
||
-- 创建定时清理过期token的事件（可选）
|
||
-- SET GLOBAL event_scheduler = ON;
|
||
-- CREATE EVENT IF NOT EXISTS clean_expired_rider_tokens
|
||
-- ON SCHEDULE EVERY 1 HOUR
|
||
-- DO
|
||
-- CALL CleanExpiredRiderTokens(); |