Mysql升级ORACLE 记录

自增主键问题

php和mysql不写主键mysql可以自动生成主键;

想用pdo批量向mysql插入数据只能每条一个pdostarment->execute

看tp5.1的源码提供的方案是

INSERT INTO TT2 (name, name2, name3) 
SELECT :data__name_0,:data__name2_0, :data__name3_0
UNION ALL
 SELECT :data__name_1,:data__name2_1,:data__name3_1
UNION ALL
 SELECT :data__name_2,:data__name2_2,:data__name3_2 

这样很可以一次查询

思考：

php连接oracle且在不使用触发器，预处理批量插入的解决方案

INSERT INTO TT2 (id, name, name2, name3) 
SELECT SEQ_TT2.nextval, TA.* FROM 
(SELECT :data__name_0,:data__name2_0,:data__name3_0 FROM DUAL 
UNION ALL
 SELECT :data__name_1,:data__name2_1,:data__name3_1 FROM DUAL 
UNION ALL
 SELECT :data__name_2,:data__name2_2,:data__name3_2 FROM DUAL) TA

然后给pdostarment传入占位符替换数组即可。避免了多次执行execute问题

Limit问题

Mysql   

<BASESQL> limit 3,5

oracle GP 2018-03-10 17:27:23

SELECT TB.* FROM (
    SELECT TA.*, ROWNUM aa FROM (
        <BASESQL>) TA 
    WHERE ROWNUM < (3+5+1)) TB 
WHERE aa > 3