Recently, there is a new project to change the existing engineering data source into mysql, and to achieve compatibility between oracle and MySQL after the transformation. Record the problems encountered in the process.
- Database Table Conversion and Data Conversion
Download PowerDesigner and Navicat Premium
2. Export user build table scripts that need to be exported through PL/SQL
3. Transform oracle's build script into mysql's build script through PowerDesigner
The reference links are as follows. https://blog.csdn.net/qq_28194001/article/details/79124090
4. Executing table-building scripts and synchronizing data through Navicat Premium
The reference links are as follows. https://blog.csdn.net/superit401/article/details/51547745
Navcat Installation Package and Cracking Tools Download: https://download.csdn.net/download/monkeysun123321/10354491
PowerDesigner Installation Pack Download: https://download.csdn.net/download/monkeysun123321/10739179
2. Summary of database differences
2.1 sequence
oracle has self-increasing sequence, but mysql does not. The same effect needs to be simulated.
Establishment of tables:
--- Building tables
CREATE TABLE `sequence` (
`name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT 'Name of sequence',
`current_value` int(11) NOT NULL COMMENT 'Current value of sequence',
`increment` int(11) NOT NULL DEFAULT '1' COMMENT 'Sequence self-increment',
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
----Create the one that gets the current value function
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 value INTEGER;
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END
$
DELIMITER ;
----Create the one that gets the next value function
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
SET current_value = current_value + increment
WHERE name = seq_name;
RETURN currval(seq_name);
END
$
DELIMITER ;
----Create the one that sets the current value function (No need at all)
DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
UPDATE sequence
SET current_value = value
WHERE name = seq_name;
RETURN currval(seq_name);
END
$
DELIMITER ;
INSERT INTO sequence VALUES ('SMS_SEQID_SEQUENCE_MQ', 1, 1); -- Add one sequence Name and initial value, and self-increment
SELECT SETVAL('SMS_SEQID_SEQUENCE_MQ', 10); -- Set specified sequence Initial value
SELECT CURRVAL('SMS_SEQID_SEQUENCE_MQ'); -- Query assignment sequence Current value
SELECT NEXTVAL('SMS_SEQID_SEQUENCE_MQ') ; -- Query assignment sequence Next value
2.2 Multilevel Recursive Calls
Oracle:
START WITH mcht_code = :parentMchtCode CONNECT BY NOCYCLE PRIOR mcht_code = parent_mcht_code
Use grammar: start with.. Connect by nocycle prior..
Mysql does not have a similar function and needs to be customized. The methods are as follows:
2.2.1 Primary keys and recursive associated foreign keys are numeric types such as int
------The primary key and the recursive associated foreign key are int Equal number type
CREATE TABLE `province` (
`id` int(10) NOT NULL,
`name` varchar(10) NOT NULL,
`pid` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of province
-- ----------------------------
INSERT INTO `province` VALUES ('1', 'Fujian', '0');
INSERT INTO `province` VALUES ('2', 'Hunan', '0');
INSERT INTO `province` VALUES ('3', 'Hubei', '0');
INSERT INTO `province` VALUES ('4', 'Changsha', '2');
INSERT INTO `province` VALUES ('5', 'Chenzhou', '2');
INSERT INTO `province` VALUES ('6', 'Wuhan', '3');
INSERT INTO `province` VALUES ('7', 'Wuchang', '3');
INSERT INTO `province` VALUES ('8', 'Xiamen', '1');
INSERT INTO `province` VALUES ('9', 'Fuzhou', '1');
INSERT INTO `province` VALUES ('10', 'Quanzhou', '1');
INSERT INTO `province` VALUES ('11', 'Minhou', '9');
INSERT INTO `province` VALUES ('12', 'Changle', '9');
INSERT INTO `province` VALUES ('13', 'Anxi', '10');
INSERT INTO `province` VALUES ('14', 'Jinjiang', '10');
INSERT INTO `province` VALUES ('15', 'Forbidden City', '13');
INSERT INTO `province` VALUES ('16', 'Ginseng', '13');
INSERT INTO `province` VALUES ('17', 'dragon lake', '15');
---Functions that query all subcategories
delimiter //
CREATE FUNCTION `getChildrenList`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM province where FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
//
//Functions that query all parent categories
delimiter //
CREATE FUNCTION `getParentList`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPar VARCHAR(1000);
SET sTemp ='';
SET sTempPar =rootId;
#Cyclic recursion
WHILE sTempPar is not null DO
#Is judgment the first, if not the first will be empty?
IF sTemp !='' THEN
SET sTemp =concat(sTemp,',',sTempPar);
ELSE
SET sTemp = sTempPar;
END IF;
SET sTemp =concat(sTemp,',',sTempPar);
SELECT group_concat(pid) INTO sTempPar FROM province where pid <> id and FIND_IN_SET(id,sTempPar)>0;
END WHILE;
RETURN sTemp;
END
//
----Query the ID All parent data
select * from province where FIND_IN_SET(id,getParentList(17));
---Query the ID All subdata
select * from province where FIND_IN_SET(id,getChildrenList(1));
2.2.2 Primary keys and recursive associated foreign keys are numeric types such as varchar
-----getChildrenList_mcht
delimiter //
CREATE FUNCTION `getChildrenList_mcht`(rootId varchar(80))
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd = rootId;
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(mcht_code) INTO sTempChd FROM Organization where FIND_IN_SET(parent_mcht_code,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
//
--- getParentList_mcht
delimiter //
CREATE FUNCTION `getParentList_mcht`(rootId varchar(80))
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPar VARCHAR(1000);
SET sTemp ='';
SET sTempPar =rootId;
#Cyclic recursion
WHILE sTempPar is not null DO
#Is judgment the first, if not the first will be empty?
IF sTemp !='' THEN
SET sTemp =concat(sTemp,',',sTempPar);
ELSE
SET sTemp = sTempPar;
END IF;
SET sTemp =concat(sTemp,',',sTempPar);
SELECT group_concat(parent_mcht_code) INTO sTempPar FROM Organization where mcht_code <> parent_mcht_code and FIND_IN_SET(mcht_code,sTempPar)>0;
END WHILE;
RETURN sTemp;
END
//
---Query all subcategories
select * from Organization where FIND_IN_SET(mcht_code, getChildrenList_mcht('M002'));
---Query all parent categories
select * from Organization where FIND_IN_SET(mcht_code,getParentList_mcht('M002001'));
To be continued...