网站制作优质公司,wordpress+主题+试用,东莞企业建站平台,第十八届杭州动漫展最近由于项目换库#xff0c;特此记录
1.字段类型 number(8) - int(8) number(16) - bigint(16) varchar2() - varchar() 2.导数据
从oracle迁移数据到mysql#xff0c;除了用专门的数据泵#xff0c;经常需要用csv导入到mysql#xff1b;
导出的csv数据如果…最近由于项目换库特此记录
1.字段类型 number(8) - int(8) number(16) - bigint(16) varchar2() - varchar() 2.导数据
从oracle迁移数据到mysql除了用专门的数据泵经常需要用csv导入到mysql
导出的csv数据如果有中文需要先用记事本编辑成UTF-8再导入。
关于如何导出正确的csv如下
1.对于有特殊字符的字段导入后提示不合法或者无法导入时
比如空格处理有些oracle导出的csv数据含有不可见的换行符char(10)以及回车符chr(13)
比如有 / \ 的一律使用如下查询消除将\改为|剔除回车、空格以及换行 replace(trim(replace(replace(id,chr(13),),chr(10),)),\,|) 如果是clob字段则先to_char 然后如上一样的处理 3.decode
oracle: update table set 字段decode() where ...
mysql: update table set 字段case when ... end where ...
4.rownum字段
如果是where条件后面比如rownum1改为 limit 1
如果是select后面比如
select rownum from dual
改为 SELECT ( SELECT rownum : rownum 1 FROM ( SELECT rownum : 0 ) rownum ) FROM DUAL 5.mysql设置默认时间的字段 select CURRENT_DATE #2024-04-01 select CURRENT_TIME #15:02:54 select CURRENT_TIMESTAMP #2024-04-01 15:03:13 6.左右连接 对于a.idb.id() 用left joina.id()b.id用right join 7.nvl函数
nvl() - ifnull()
8.merge函数
这个比较有意思mysql8没有能够实现的函数代码里多写几个接口吧
9.关于时间的sql改造
记得有一次从oracle导入时间的数据到了mysql发现时间少了8小时注意下东八区GMT8问题 整型 to_number(id) 改为 cast(id as unsigned int) 浮点型to_number(id) 改为 cast(id as decimal(10,2)) 数字转字符串to_char(id) 改为 convert(id,char) 时间之类的 to_char(sysdate,yyyy-mm-dd). 改为 date_format(字段,%Y-%m-%d) (yyyy-mm-dd hh24:mi:ss) - (%Y-%m-%d %H:%i:%s) 或者(%Y-%m-%d %T) 对于oracle的timestamp类型的数据查询导出时候需要转成年月日时分秒 因为数据长这样21-4月 -21 02.15.29.556170 下午 这样导入mysql会报错 to_char(wo_date,yyyy-mm-dd hh24:mi:ss) 然后mysql里字段相应设置为datetime/timestamp即可注意时区问题 如果是年月日mysql对应为date类型 10.关于时间sql运算改造
有趣的是oracle的timestamp类型字段可以直接加减乘除对有的业务需要除
比如字段1则直接按天加 字段名1 mysql里得这样 date_add(sysdate(),interval 1 day)减写成-1 这个是按天按月如下 date_add(date_format(2024-01-02,%Y-%m-%d),interval -8 month) 两个时间相差天数 datediff(time1,time2) 两个日期相差月数 timestampdiff(month,字段,now()) 11.trunc函数 trunc(1.2233)对应mysql truncate(1.2233,0). 0默认保留整数位 trunc(日期)对应date_format(日期,%Y-%m-%d) 12.拼接函数
mysql通过concat(字段,字段) 实现拼接 ||
13.start with ... connect by
这个函数着实用了不少时间改造因为mysql5根本无法实现必须是mysql8以上的。
这个用于带有组织表数据的分组函数prior跟随父字段查该条件以上节点否则查子节点
比如 select orgId from org z start with z.orgid32401 connect by prior z.orgid z.parentorgId; 改造成mysql需要 使用 with recursive函数 with recursive z1(orgId) as ( select z0.orgId from org z0 where z0.orgid32401 union alll select z2.orgid from org z2, z1 where z2.parentorgId z1.orgId ) select * from z1; 如果查询父节点 则绿色部分改为 z2.orgIdz1.parentorgId
14.nulls last/first order by id nulls last. - order by if(isnull(id),1,0),id order by id nulls first - order by if(isnull(id),0,1),id 15.connect bylevel oracle比如 select sysdate1-rownum sday,level from dual connect by level8; 这个忘记是什么业务了不好直接改可以先建个表名叫temp_num字段只有一个rn(int)然后数据是1-100000差不多够用就行这个辅助表会方便很多
后期看到直接套以下语句即可 select rn,date_format(date_add(now(),interval rn-8 day),%Y-%m-%d %T) sday, 81-rn from temp_num tn where rn8; 再来一组 select trunc(to_date(#{lastDate},yyyy-mm-dd)) - level 1 DDATE from dual connect by level#{dateSize} 复杂了点改为 select date_format(date_add(#{lastDate}, interval rn-#{dateSize} day), %Y-%m-%d) DDATE from temp_num tn where rn#{dateSize} 再来一组复杂的 select (to_date(2024-01-01,yyyy-mm-dd) level -1 ) as every_day from dual connect by level(trunc(to_date(2024-02-01,yyyy-mm-dd)) - trunc(to_date(2024-01-01,yyyy-mm-dd)) 1) 这里的日期是实际上是作为参数传递的这样写可以直接测试用 改为 SELECT rn,date_add( date_format( 2024-01-01, %Y-%m-%d ), INTERVAL datediff( date_format( 2024-02-01, %Y-%m-%d ), date_format( 2024-01-01, %Y-%m-%d ))1-rn DAY ) AS every_day FROM temp_num tn WHERE rn datediff(date_format( 2024-02-01, %Y-%m-%d ),date_format( 2024-01-01, %Y-%m-%d )) 1 再比如 SELECT add_months ( to_date ( 2024-01-01, yyyy-mm-dd ),- LEVEL 1 ) dates FROM DUAL connect BY LEVEL 12 改为 SELECT date_add(date_format(2024-01-01,%Y-%m-%d),interval rn-12 month) as dates from temp_num tn where rn12 上述代码如果复制运行报错则是空格导致的删除空格重新写内容没有问题
16.listagg函数 listagg within group 改为 group_concat listagg(字段, ;) 改为 group_concat(字段 separator ;) 再比如复杂一点的 listagg(to_char(stat_date,MM/dd) || ! || flag, ,) within group(order by stat_date) as data 改为 group_concat(concat(date_format(stat_date,%m/%d),!,flag) order by stat_date SEPARATOR ,) as data 17.释放表空间 delete from table 对应myisam会立刻释放表空间InnoDB不会 所以在delete之后执行optimize table tableName会释放 如果optimize不支持 先alter table tableName engineInnoDB, 再 analyze table tableName
