oracle表转换mysql建表语句

先上代码：

select  --t1.table_name,

       case when t1.COLUMN_ID <> 1 then '   ,'||lower(t1.COLUMN_NAME)||'  ' else 'create table  ' || t1.table_name || ' ( ' ||lower(t1.COLUMN_NAME)||'  ' end||
                      case
                                  when t1.DATA_TYPE like '%VARCHAR%' then 'VARCHAR'
                                  when t1.DATA_TYPE like '%DATE%' then 'DATETIME'
                                  when t1.DATA_TYPE like '%NUMBER%' then 'INT'
                                  when t1.DATA_TYPE like '%TIMESTAMP%' then 'TIMESTAMP'
                          else '无法识别的类型' end|| ' ( '||t1.data_length||' )'||
                     'null comment '||''''||t2.comments||'''' || case when t1.COLUMN_ID=t3.COLUMN_ID then ')'||'comment' || ''''||t3.comments || ''' ;' end as txt
                from all_tab_columns t1
                left join all_col_comments t2
                  on t1.owner = t2.owner
                 and t1.table_name = t2.table_name
                 and t1.column_name = t2.column_name
                left join (select max(COLUMN_ID) COLUMN_ID ,a.owner, b.table_name,comments from  all_tab_columns a
                    left join all_tab_comments b on a.owner = b.owner and a.table_name = b.table_name
                group by a.owner, b.table_name,comments )t3
                  on t1.owner = t3.owner
                 and t1.table_name = t3.table_name
               where t1.table_name in ('TABLE_NAME1','TABLE_NAME2','TABLE_NAME3')
                 and t1.owner = upper('db_name')
               order by t1.table_name,t1.column_id;

解释一下，这个sql主要是通过 all_tab_columns和all_col_comments两个数据字典，获取对应的表名、列表、类型、长度、注释，转换成mysqlDDL语句。

此代码没有进行主键判断，因为在我的工作中已经够用了，各位如果要转换的表很多那就得关联上user_constraints数据字典了

代码复制下来就能用，把table_name,和db_name换成要转换的表面和库名就行。注意表名大小写。