Question

怎样让mysql主键由日期+时间+4位自增序号组成

Answer 1

可以在mysql中创建一个sequence，然后再查询

第一步：创建--Sequence 管理表
DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
name VARCHAR(50) NOT NULL,
current_value INT NOTNULL,
increment INT NOT NULLDEFAULT 1,
PRIMARY KEY (name)
) ENGINE=InnoDB;
第二步：创建--取当前值的函数
DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE valueINTEGER;
SET value = 0;
SELECT current_value INTOvalue
FROMsequence
WHEREname = seq_name;
RETURN value;
END
$
DELIMITER ;
第三步：创建--取下一个值的函数
DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
UPDATE sequence
SETcurrent_value = current_value + increment
WHEREname = seq_name;
RETURN currval(seq_name);
END
$
DELIMITER ;
第四步：创建--更新当前值的函数
DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), valueINTEGER)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
UPDATE sequence
SETcurrent_value = value
WHEREname = seq_name;
RETURN currval(seq_name);
END
$
DELIMITER ;
接下来，就可以了
SELECT concat(date_format(NOW(),'%Y%m%d%H%i%s'),lpad((SELECT NEXTVAL('TestSeq')),4,0))
--201707181025200020