ALTER TABLE ZFTJ_HALF MODIFY CREATE_TIME TIMESTAMP WITH LOCAL TIME ZONE;
点击开始,进行表转换
##创建序列
CREATE TABLE
IF NOT EXISTS `sequence` (
`name` VARCHAR (50) NOT NULL,
`current_value` INT (11) NOT NULL,
`increment` INT (11) NOT NULL DEFAULT '1'
);
##当前值
DROP FUNCTION IF EXISTS `currval`;
CREATE FUNCTION `currval` (seq_name VARCHAR(50)) RETURNS INT (11)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE VALUE INTEGER ;
SET VALUE = 0 ;
SELECT current_value INTO VALUE FROM sequence WHERE NAME = seq_name ;
RETURN VALUE;
END ;
##下一值
DROP FUNCTION IF EXISTS `nextval`;
CREATE FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS int(11)
DETERMINISTIC
BEGIN
UPDATE sequence SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN currval(seq_name);
END;
##插入序列
INSERT INTO `sequence` (
`name`,
`current_value`,
`increment`
)
VALUES
('ZFTJ_HALF_SEQ', 0, 1);
SELECT currval('ZFTJ_HALF_SEQ');
SELECT nextval('ZFTJ_HALF_SEQ');
对于其他表,在转换成mysql后,确认一下表中数据id的最大值,将最大值+1作为新表序列的current_value。然后自定义序列名,最好与原序列名称保持一致,执行步骤4.4。将原sql中的SELECT ZFTJ_SEQ.nextval AS id FROM DUAL
替换为新序列的nextvalSELECT nextval('ZFTJ_HALF_SEQ') AS ID
##创建序列
CREATE TABLE
IF NOT EXISTS `sequence` (
`name` VARCHAR (50) NOT NULL,
`current_value` INT (11) NOT NULL,
`increment` INT (11) NOT NULL DEFAULT '1'
);
##插入序列
INSERT INTO `sequence` (
`name`,
`current_value`,
`increment`
)
VALUES
('ZFTJ_HALF_SEQ', 0, 1);
##当前值
DROP FUNCTION IF EXISTS `currval`;
CREATE FUNCTION `currval` (seq_name VARCHAR(50)) RETURNS INT (11)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE VALUE INTEGER ;
SET VALUE = 0 ;
SELECT current_value INTO VALUE FROM sequence WHERE NAME = seq_name ;
RETURN VALUE;
END ;
##下一值
DROP FUNCTION IF EXISTS `nextval`;
CREATE FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS int(11)
DETERMINISTIC
BEGIN
UPDATE sequence SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN currval(seq_name);
END;
SELECT currval('ZFTJ_HALF_SEQ');
SELECT nextval('ZFTJ_HALF_SEQ');