Oracle to MySQL数据库迁移方案
Oracle to MySQL数据库迁移方案
1 信息收集
1.1 迁移指标
1.1.1 是否允许停机
1.1.2 是否考虑回退
1.2 Oracle数据库基本信息收集
2.1.1 数据库基本信息收集
针对待迁移的数据库进行基本信息采集，了解当前数据库的运行状态、规模以及架构等，可对Oracle数据库有一个基础的判断，是数据迁移工作的首要步骤。
- 数据库版本号
n 了解当前数据库的版本信息，以便针对版本进行数据迁移工具选型及与MySQL兼容性对比
- 数据库字符集
n 了解当前数据库层字符集设定，以便在数据迁移过程中适配相应字符集防止数据乱码及保持MySQL端字符集兼容
- 数据库操作系统平台
n 了解当前数据库操作系统平台，以便针对不同平台进行迁移软件选型评估
n 部分迁移软件对操作系统类型、版本有相应限制
- 数据库名称
n 了解待迁移的database或是schema名称，以此与MySQL端一一对应
n 需要注意的是Oracle端一个用户或schema通常对应MySQL端一个数据库
- 数据库归档模式
n 了解数据库是否开启归档，以便确定在做迁移时是否会涉及数据库重启操作
n 如需要基于REDO LOG/ARCHIVE LOG进行数据迁移则必须开启数据库归档
- 数据库架构
n 了解当前数据库高可用架构，如是否为RAC架构，是否为DataGuard复制架构
n 基于不同架构可灵活调整数据迁移方案以及是否涉及到数据库停机时间
n 如存在DataGuard架构则数据迁移可基于DataGuard节点进行数据抽取及同步
- 数据库大小
n 了解当前数据库总大小以及待迁移的数据库大小
n 用于评估迁移的复杂度、时间以及空间占用
- 数据库中大表最大记录数及TOP10大表
n 了解当前数据库中待迁移表是否存在大表以及大表的数据量规模
n 用来评估是否需要针对大表进行额外处理
n 如数据库总大小为1T,其中一个访问日志流水表占用300G,经过评估分析可不进行迁移或通过部分迁移的方式抽取数据。
2.1.2 业务涉及的数据库对象复杂性
针对待迁移的数据库内部对象，需要进行统计分析，了解当前数据库迁移复杂度，数据库对象是否与业务逻辑存在耦合关系，该部分的复杂及耦合程度的高低是评估迁移难易程度的重要依据。
l 存储过程数量及复杂度
n 统计当前数据库中存储过程的数量(包括统计出失效存储过程的数量)、调用频率以及复杂程度
n 用来评估存储过程是否涉及业务逻辑、是否可拆分或使用业务代码实现
n MySQL端不建议使用包含业务逻辑的存储过程，增加业务与数据库的耦合性
l 触发器数量及复杂度
n 统计当前数据库中触发器的数量(包括失效的数量)、调用频率及复杂程度
n 用来评估触发器是否涉及业务逻辑、是否可拆分或使用业务代码实现
n MySQL端不建议使用包含业务逻辑的触发器，增加业务与数据库的耦合性
l 视图数量及复杂度
n 统计当前数据库视图数量及应用场景
n 用来评估是否可进行视图拆分及用业务代码实现
n MySQL端不建议使用包含业务逻辑的视图，增加业务与数据库的耦合性
l 物化视图数量及复杂度
n 统计当前数据库中物化视图的数量及应用场景
n 用来评估是否需要针对物化视图进行改造或使用业务代码实现
n MySQL端不支持物化视图，对于这类场景数据可采用缓存形式进行改造
l 自定义序列数量及使用逻辑
n 统计当前数据库中自定义序列的使用场景及数量
n 用来评估是否可基于MySQL自增序列进行改造或基于业务代码层生成序列值
l DBLINK数量
n 统计当前数据库是否存在DBLINK及DBLINK的数量
n 用来评估是否存在多系统相互依赖或不同数据库相关迁移表之间的依赖关系
n 如业务涉及DBLINK至其他数据库中库表，则需要一并进行迁移并进行业务改造
l 分析类SQL语句数量及执行效率
n 统计是否存在分析类业务(OLAP)以及该类业务的执行频率、数量及效率等
n MySQL对OLAP类业务支持力度较弱，针对该类场景需要进行评估是否在MySQL端处理或将该功能切分至数据分析平台，如hadoop等。
2.2.3 Oracle与MySQL的兼容性
针对待迁移的Oracle数据库基本信息，结合MySQL的一些特性及与Oracle的差异性，对Oracle中不满足MySQL相关设计规范的数据库对象进行筛选统计，可在数据迁移前期有效的规避大部分兼容性问题。对于MySQL的库表设计规范，可参考第6节中6.3小节《MySQL库表设计规范》文档附录。
l 是否存在字段函数默认值
n 统计数据库中各字段是否存在函数默认值及具体逻辑
n MySQL默认仅支持对日期类型字段设定函数默认值，如Oracle中sys_uuid、check约束等，针对该类属性需进行业务改造，将该部分逻辑交由业务代码实现或进行逻辑判断
l 是否存在大小写敏感/特殊字符的表名、字段名
n Oracle支持对表及字段名使用双引号来创建非标准的表名(大写字母+数字+下划线)
n MySQL设计规范建议对表及字段名称忽略大小写且建议表及字段不包含特殊字符
n 针对Oracle端存在大小写敏感及有特殊字符的表,建议在数据迁移过程中进行转换并结合业务代码一并改造使之符合MySQL库表设计规范
l 是否存在超过MySQL限制的的表名、字段名
n 默认Oracle仅支持30个字符的表名长度，MySQL最高支持64个字符
n 考虑数据回退场景，建议MySQL端在创建表名及字段名称时也不超过30个字符
l 是否存在长度超过MySQL限制的索引
n MySQL在不同字符集下对单个索引有不同的长度限制
u UTF8 = 3072/3 = 1024个字符
u UTF8MB4 = 3072/4 = 768个字符'
n 对于超过长度限制的索引，在MySQL端需考虑使用前缀索引或缩短数据类型
l 是否存在无主键或唯一键的表
n Oracle默认为堆表结构，索引与数据分离，MySQL为索引组织表，索引即数据
n MySQL设计规范建议各表均采用自增ID作为主键，如Oracle端无主键或唯一键在数据迁移过程中存在性能问题以及数据不一致风险。
l 是否存在VARCHAR2长度超过MySQL限制的表
n MySQL在不同字符集下对单表VARCHAR字段长度有所限制
u UTF8 = 65535/3 ≈ 21800
u UTF8MB4 = 65535/4 ≈ 16000
n 针对Oracle端超过该限制的表，需对部分字段进行长度缩减或修改字段类型
l 是否存在MySQL不支持或不完全兼容的数据类型
n Oracle端存在部分特殊数据类型及部分数据类型精度超过MySQL限制
u TIMESTAMP(7)、TIMESTAMP(8)
u RAW
u UROWID
u BLOB
u BFILE
n 针对特殊的数据类型需结合业务实际情况进行调整成MySQL兼容的数据类型，部分类型如BFILE由于保存大文件至数据库，不适用于MySQL数据库，则还需涉及业务逻辑改造。
2.2.4 数据迁移依赖统计
数据迁移通常采用以下2种逻辑进行数据抽取及同步
- 基于数据库日志解析进行全量及增量数据同步
- 基于数据库中某字段判断数据是否更新以便进行数据增量同步
基于场景2，需要数据库各待迁移表包含modify_date时间类型字段，该字段表示当表中任意一条记录被修改时，该时间字段均会自动更新，从而在全量抽取完成后可基于该字段进行增量数据的区分及抽取过滤。
2.工具选型
2.1 表结构转换
在数据迁移流程中，表结构转换作为数据同步的前提，其转换的效率及准确性直接影响着数据同步是否能正常进行及数据是否能成功迁移，以下针对2种不同的表结构转换逻辑进行对比，并最终选择最优的转换方案。
Oracle与MySQL的数据类型不尽相同，对于各类不同的数据类型的转换，我们总结了一份Oracle与MySQL之间的数据类型转换mapping关系表，各类型对应关系可查看第6章中6.1小节《数据类型mapping关系表》文档附录。
2.1.1 基于mapping关系人工转换
在大部分数据迁移示例场景中，表结构转换均以单个表作为转换示例，介绍如何进行表字段数据类型的匹配及转换，而针对大批量的转换场景，人工转换则可能效率低下。以下是人工基于mapping进行表结构转换的优缺点
- 对于表数量较少时(通常为100张以下)转换较为便捷
- 转换方式灵活，可自行增加附加字段
- 在mapping关系正确的情况下，人工转换表结构准确性高
- 对于表数据较多时(通常为100张以上)转换较为耗时
2.1.2 基于sqlines工具批量转换
sqlines是专门针对异构数据库之间表结构转换、数据迁移及数据库对象转换的开源工具，工具部署方便，操作简单。该方案仅针对其支持的异构数据库之间表结构转换特性进行对比。
- 可批量对指定数据库表进行转换，转换效率高
- 支持通配符和正则表达式进行表过来
- 支持自定义mapping数据类型映射关系
- 支持自定义表名及字段名转换
- 对数据类型的转换存在放大问题
n 如Oracle的number不指定精度则转换为MySQL中double类型
n 该问题可自定义数据类型mapping关系进行规避
- 字段默认值可能丢失导致表结构默认属性不一致
2.1.3 表结构转换工具选型
综合2种不同的表结构转换方式，可以发现其各有优缺点，通过实际验证后最终采用以下方案
- 首先采用sqlines对需要迁移的表进行批量转换
- 对sqlines转换完成的表结构进行人工微调校验
n 确保数据类型转换正确
n 确保字段默认值属性正确
2.2 数据迁移
数据迁移工具或方式的选型决定了数据迁移的难易程度，选择一个正确的数据迁移工具能有效的避免迁移过程中的各种异常情况，以下针对4种不同的数据迁移方式或工具进行特性分析，以及结合实际数据迁移的时效性、业务连续性等要求最终确定数据迁移逻辑。
2.2.1 文本文件导入导出
绝大多数数据库均支持对数据进行逻辑的文本文件导出和导入，如Oracle数据库的sqlldr工具，如MySQL的mysqlexport及mysqlimport工具，以下是文本文件导入导出的优缺点
- 导入导出效率极高，无需关心表之间的关联逻辑
- 从Oracle数据库中导出数据较为繁琐，需要对各表进行字段拼接处理
- 导入导出文件中数据之间的分隔符和界定符需确保不与数据冲突
n 分隔符过于简单容易与数据冲突导致导入数据时字段匹配出错
n 分隔符过于复杂则会导致导出的数据文件显著增大
n 导出时不支持BLOB等大对象数据类型
n 对增量数据处理上不友好，如源端对数据进行删除，则无法识别出
2.2.2 MySQL Migration Toolkit工具
MySQL Migration Toolkit工具是Oracle官方早期提供的异构数据库表结构转换及数据迁移工具，该工具通过流程化的操作，逐步完成数据的迁移配置，对用户友好，支持多种类型数据库之间数据交换并生成转换语句，以下为该工具的优缺点
- 图形化界面操作，操作简单且对用户友好
- 仅支持Windows平台，兼容性较差
- 对大批量数据的迁移会导致软件崩溃
- 该软件由10多年前开发，多年前停止维护，目前已用workbench取代部分功能
- 表结构转换的准确性偏低且大部分数据类型均会进行数据类型定义放大的问题
- 对增量数据处理上不友好，如源端对数据进行删除，则无法识别出
- 对BLOB、CLOB类型数据支持较差
2.2.3 kettle开源ETL工具
Kettle是基于java开发的免费且开源的ETL工具，不仅支持多种不同数据库直接数据转换，还支持大数据、文本、日志、自定义函数、自定义变量等各类非数据库数据处理，具有强大的数据处理、清洗、转换功能，以下为该工具的优缺点
- 支持Windows、Linux、Unix多平台
- 图形化操作管理，无需编写代码，仅通过界面拖拽即可
- 支持定时触发迁移、批量job迁移
- 支持自定义表结构mapping关系
- 对增量数据处理上不友好，依赖modify_date字段对源端变更数据做识别,如源端对数据进行删除，则无法识别出
2.2.4 Oracle GoldenGate
OGG全称为Oracle GoldenGate,是由Oracle官方提供的用于解决异构数据环境中数据复制的一个商业工具。相比于其它迁移工具OGG的优势在于可以直接解析源端Oracle的redo log，因此能够实现在不需要对原表结构做太多调整的前提下完成数据增量部分的迁移。以下为该工具的优缺点及特性
- Oracle原生商业异构数据同步工具
- 有完善的产品文档及故障处理逻辑
- 针对多种不同场景的数据迁移有与之相应的解决方案参考
n 多个库汇聚为一个库
n 一个库分散至库多个
n 一个库同步至一个库
n 一个库分发至多个不同类型数据库
- 软件支持多种平台
- 异构场景下对全量数据同步效率较差
n 可通过开启多个并发同步进程提升效率
- 支持自定义表结构及字段mapping关系
- 支持实时监控各同步进程状态，便于故障诊断
- 完整支持全量及增量迁移、断点续传
- 不支持图形化界面配置，文本方式配置较为繁琐
- 可Oracle其他软件产品结合使用，如Dataguard
2.2.5 数据迁移工具选型
综合以上4种数据迁移工具及方式，可以了解到使用文本文件导入导出以及MySQL Migration Toolkit均有较大局限性且兼容性和可维护性均较差，基本可以被排除在外。综合考虑具体的迁移要求及业务场景，以及大部分情况下Oracle数据库均未配置modify_date字段用以区分变更后的数据，使用kettle无法有效的识别增量数据，最终采用以下方式进行数据迁移
- 使用Oracle GoldenGate作为数据全量和增量的主要同步工具
n 全量数据也可采用kettle方式进行同步
n 增量数据必须采用GoldenGate解析redo log的方式进行同步
- Oracle端数据采用从DataGuard的方式抽取，减少对主库的影响
- 同步期间支持对数据进行静态校验校验(结合Oracle DataGuard架构)
3.数据校验
3.1 基于业务逻辑校验
基于业务逻辑的数据校验分为以下2部分，具体校验逻辑及维度需业务人员提供具体业务场景，
- 基于业务维度对数据进行汇总统计，如
a) 当天/当周/当月交易金额
b) 会员数量/等级
c) 各表总记录数
- 创建小批测试用户进行业务逻辑的验证，确保Oracle与MySQL端产生的结果一致，如
a) 测试会员注册
b) 测试会员交易成功/失败
c) 测试会员等级变更
3.2 基于CRC32一致性校验
通过将表中记录的关键字段值拼接成字符串,然后计算该字符串CRC32校验码，再将所有记录的校验码通过逻辑异或进行汇聚，生成该表所有记录关键字段的校验码，从而确保数据迁移的数据完整性和一致性。下面是对一张表的数据校验SQL示例
- MySQL:
|
SELECT BIT_XOR(CRC32(CONCAT_WS('',`字段1`,`字段2`,`字段3`,`字段...`))) FROM TabName; |
- Oracle:
|
SELECT BIT_XOR(CRC32(字段1||字段2||字段3||字段...))) FROM TabName; |
- Oracle需要通过用户自定义聚合函数及配置外部动态库方式实现BIT_XOR和CRC32算法。
- 结果示例
3.3 基于Oracle GoldenGate统计信息校验
Oracle GoldenGate
- 全量数据验证
n Oracle GoldenGate全量抽取、回放线程执行完成后会生成一个report文件，可用该文件来判断全量抽取及全量同步是否存在异常,当输出文件中discards记录为0表示没有异常数据生成，数据正常同步成功
n 通过对比全量抽取和全量回放日志中各表的insert字段来判断抽取的记录数、回放的记录数以及抽取和回放过程中是否存在数据异常
- 增量数据验证
n Oracle GoldenGate增量抽取、回放过程同样会生成处理的统计信息，通过对统计信息进行过滤，可以了解到数据同步的整体状态、单表同步状态以及是否存在同步异常等，精确追踪和诊断数据同步问题。
3.4 数据校验逻辑选型
该方案采用Oracle GoldenGate结合Oracle DataGuard进行数据抽取同步，决定采用以下方式进行数据校验
- 全量数据校验
- 待全量数据采用GoldenGate同步完成后，停止DataGuard与Oracle主库的同步
- 采用统计GoldenGate全量同步report文件的方式判断是否存在同步失败的数据
- 采用crc32的方式对Oracle和MySQL端表全字段进行一致性校验
- 增量数据校验
- 在T-1日期间停止停止DataGuard与Oracle主库的同步
- 采用GoldenGate增量同步统计信息对增量数据判断是否存在同步失败的数据
- 采用crc32的方式对Oracle和MySQL端表全字段进行一致性校验
- 基于业务逻辑及测试用户进行数据及业务代码验证
- 开启DataGuard数据同步
- 在T日数据同步割接后再次重复以上操作，完成最终的数据一致性校验
4.数据回退
在绝大部分数据库迁移项目中，数据回退仅作为最终不得已的手段，对已迁移的业务及业务数据回退至老系统中，回退的代价较大，对于数据库而言则需要识别出哪些数据是迁移后产生的并重新回放至源端老系统中，以下分别针对3种数据回退方式进行对比。
4.1 基于MODIFY_DATE字段获取新增数据
待源端Oracle数据迁移至目标端数据库时，为符合MySQL开发设计规范，将在MySQL数据库各表字段中新增CREATE_DATE、MODIFY_DATE字段，其中MODIFY_DATE则用于当该表任意一行记录做修改时，自动更新该行记录的MODIFY_DATE时间，则基于该字段可识别出至数据迁移完成后，存在哪些表的哪些数据做过更新，将这部分数据识别出来导出并重新导入至Oracle数据库中完成数据回退。该方式有以下特点
- 需对每张表进行查询匹配MODIFY_DATE时间
- 每张表更新的数据均需要进行处理并编写Oracle端导入语句
- 对BLOB或特殊字符数据支持力度不够
4.2 基于binlog2sql解析SQL语句
MySQL开源工具binlog2sql可基于binlog日志进行解析，将执行过的DML语句解析成标准的SQL语句，基于该工具将Oracle涉及的表的所有DML更新均实时解析并生成一份完整的回退SQL文件，最终将该SQL文件导入至Oracle数据库中，该方式有以下特点
- 需对binlog做库表过滤解析
- 对源端和目标端字段不一致时生成的SQL语句兼容性较差
- 解析生成的SQL语句需进行额外特殊注释信息替换方可支持Oracle语法
4.3 基于Oracle GoldenGate反向同步
Oracle GoldenGate不仅支持Oracle数据库实时同步数据至MySQL，也支持抓取MySQL日志解析同步至Oracle数据库中，通过相同的逻辑可将割接后的MySQL的增量数据实时抓取保存，如涉及数据回退操作，则开启MySQL至Oracle端的同步进程，将数据反向同步至Oracle端即可，且同步逻辑和直接参考数据迁移流程。
4.4 数据回退逻辑选型
基于数据迁移工具选型用Oracle GoldenGate，数据回退仍旧可以用的Oracle GoldenGate反向从MySQL同步数据到Oracle的逻辑进行处理，以及在数据割接期间同时记录割接时间，便于后续如需做基于时间点的数据回退时可仍旧采用MODIFY_DATE字段或binlog2sql工具，有效的将三种不同的回退逻辑结合。
5.常见问题
5.1 timestamp字段精度过长
Oracle timestamp类型精度可精确到秒后面7位(timestamp(7)),MySQL datetime数据类型仅支持到6位，转换时报错Too-big
precision 7 specified for 'READ_TIME'. Maximum is 6.
修复方式：
编辑sqlines的 sqlines_cmap.txt文件增加字段数据类型转换细节，只保留6位，后续数据同步时也需对数据进行截断处理
test.table1,table1,datetime(6)
5.2 表及字段中存在特殊字符
Oracle可以通过创建表时增加双引号来创建有特殊字符的表，具体表现为
- 有中划线的表，如:
GY-tbl-basic_org - 有区分大小的写表如:
GY-tbl_ib_basic ·有特殊字符的表，如:EIMP_$DAB
修复方式：
1.在使用sqlines时可在tf文件中也同时对需要获取的Oracle表增加双引号，但在转换为MySQL结构时，由于MySQL采用的是反引号作为特殊字符表的创建方式，所以无法创建成功，需要将存在特殊字符的表获取处理手工转换。
2.对于存在特殊字符或存在大小的表，建议修改为符合MySQL设计规范的结构，如小写字母+下划线+数字的组合形式。
5.3 索引字符长度过长
|
### PARENT_IDS 为varchar(2000) CREATE INDEX SYS_OFFICE_PARENT_IDS ON upi_asset.SYS_OFFICE (`PARENT_IDS`); ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes |
MySQL针对varchar类型进行索引有长度限制，对于UTF8时，一个字符占用3个直接，UTF8MB4占用4个字节，MySQL限定索引最大长度为3072个字节，所以对应字符集的最大可索引的varchar长度为：
- UTF8 varchar(1024) 1024*3=3072
- UTF8MB4 varchar(768) 768*4=3072
3072字节的来源：
innodb的page默认大小为16k，由于采用b+tree结构，为了不退化为链表，要求子节点一个page最好包含两条记录，所以一个记录不能超过8k。又由于聚簇索引，在二级索引中包含主键索引，所以单个索引不能超过4k，去除预留和辅助空间，取值3072。
6.文档附录
6.1 数据类型mapping关系表
|
Oracle |
MySQL |
|||
|
1 |
BFILE |
Pointer to binary file, ⇐ 4G |
VARCHAR(255) |
|
|
2 |
BINARY_FLOAT |
32-bit floating-point number |
FLOAT |
|
|
3 |
BINARY_DOUBLE |
64-bit floating-point number |
DOUBLE |
|
|
4 |
BLOB |
Binary large object, ⇐ 4G |
LONGBLOB |
|
|
5 |
Fixed-length string, 1 ⇐ n ⇐ 255 |
|||
|
6 |
CHAR(n), CHARACTER(n) |
Fixed-length string, 256 ⇐ n ⇐ 2000 |
VARCHAR(n) |
|
|
7 |
CLOB |
Character large object, ⇐ 4G |
LONGTEXT |
|
|
8 |
DATE |
Date and time |
DATETIME |
|
|
9 |
DECIMAL(p,s), DEC(p,s) |
Fixed-point number |
DECIMAL(p,s), DEC(p,s) |
|
|
10 |
DOUBLE PRECISION |
Floating-point number |
DOUBLE PRECISION |
|
|
11 |
FLOAT(p) |
Floating-point number |
DOUBLE |
|
|
12 |
INTEGER, INT |
38 digits integer |
INT |
DECIMAL(38) |
|
13 |
INTERVAL YEAR(p) TO MONTH |
Date interval |
VARCHAR(30) |
|
|
14 |
INTERVAL DAY(p) TO SECOND(s) |
Day and time interval |
VARCHAR(30) |
|
|
15 |
LONG |
Character data, ⇐ 2G |
LONGTEXT |
|
|
16 |
LONG RAW |
Binary data, ⇐ 2G |
LONGBLOB |
|
|
17 |
NCHAR(n) |
Fixed-length UTF-8 string, 1 ⇐ n ⇐ 255 |
NCHAR(n) |
|
|
18 |
NCHAR(n) |
Fixed-length UTF-8 string, 256 ⇐ n ⇐ 2000 |
NVARCHAR(n) |
|
|
19 |
NCHAR VARYING(n) |
Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000 |
NCHAR VARYING(n) |
|
|
20 |
NCLOB |
Variable-length Unicode string, ⇐ 4G |
NVARCHAR(max) |
|
|
21 |
NUMBER(p,0), NUMBER(p) |
8-bit integer, 1 <= p < 3 |
TINYINT |
(0 to 255) |
|
16-bit integer, 3 <= p < 5 |
SMALLINT |
|||
|
32-bit integer, 5 <= p < 9 |
INT |
|||
|
64-bit integer, 9 <= p < 19 |
BIGINT |
|||
|
Fixed-point number, 19 <= p <= 38 |
DECIMAL(p) |
|||
|
22 |
NUMBER(p,s) |
Fixed-point number, s > 0 |
DECIMAL(p,s) |
|
|
23 |
NUMBER, NUMBER(*) |
Floating-point number |
DOUBLE |
|
|
24 |
NUMERIC(p,s) |
Fixed-point number |
NUMERIC(p,s) |
|
|
25 |
NVARCHAR2(n) |
Variable-length UTF-8 string, 1 ⇐ n ⇐ 4000 |
NVARCHAR(n) |
|
|
26 |
RAW(n) |
Variable-length binary string, 1 ⇐ n ⇐ 255 |
BINARY(n) |
|
|
27 |
RAW(n) |
Variable-length binary string, 256 ⇐ n ⇐ 2000 |
VARBINARY(n) |
|
|
28 |
REAL |
Floating-point number |
DOUBLE |
|
|
29 |
ROWID |
Physical row address |
CHAR(10) |
|
|
30 |
SMALLINT |
38 digits integer |
DECIMAL(38) |
|
|
31 |
TIMESTAMP(p) |
Date and time with fraction |
DATETIME(p) |
|
|
32 |
Date and time with fraction and time zone |
|||
|
33 |
UROWID(n) |
Logical row addresses, 1 ⇐ n ⇐ 4000 |
VARCHAR(n) |
|
|
34 |
VARCHAR(n) |
Variable-length string, 1 ⇐ n ⇐ 4000 |
VARCHAR(n) |
|
|
35 |
VARCHAR2(n) |
Variable-length string, 1 ⇐ n ⇐ 4000 |
VARCHAR(n) |
|
|
36 |
XMLTYPE |
XML data |
LONGTEXT |
|
6.2 常用函数mapping关系表
|
Oracle |
MySQL |
||
|
1 |
ABS(num) |
Get the absolute value |
ABS(num) |
|
2 |
ACOS(num) |
Get the arc cosine |
ACOS(num) |
|
3 |
ADD_MONTHS(date, num) |
Add num months to date |
TIMESTAMPADD(MONTH, num, date) |
|
4 |
ASCII(str) |
Get ASCII code of left-most char |
ASCII(str) |
|
5 |
ASCIISTR(string) |
Get ASCII code version of string |
|
|
6 |
ASIN(num) |
Get the arcsine |
ASIN(num) |
|
7 |
ATAN(num) |
Get the arc tangent |
ATAN(num) |
|
8 |
ATAN2(x, y) |
Get the arc tangent of x and y |
ATAN2(x, y) |
|
9 |
BIN_TO_NUM(bit1, bit2, …) |
Convert bit vector to number |
|
|
10 |
BITAND(exp1, exp2) |
Perform bitwise AND |
(exp1 & exp2) |
|
11 |
CEIL(num) |
Get the smallest following integer |
CEIL(num) |
|
12 |
CHR(num) |
Get character from ASCII code |
CHAR(num USING ASCII) |
|
13 |
COALESCE(exp1, exp2, …) |
Return first non-NULL expression |
COALESCE(exp1, exp2, …) |
|
14 |
CONCAT(char1, char2) |
String concatenation |
CONCAT(char1, char2) |
|
15 |
CONVERT(string, charset) |
Convert string to charset |
CONVERT(string USING charset) |
|
16 |
COS(num) |
Get the cosine |
COS(num) |
|
17 |
COSH(num) |
Get hyperbolic cosine |
(EXP(num) + EXP(-num)) / 2 |
|
18 |
CURRENT_DATE |
Get the current date |
NOW() |
|
19 |
CURRENT_TIMESTAMP |
Get the current date and time |
NOW() |
|
20 |
DECODE(exp, when, then, …) |
Evaluate conditions |
CASE expression |
|
21 |
EXP(n) |
Raise e to the nth power |
EXP(n) |
|
22 |
EXTRACT(YEAR FROM date) |
Extract year from date |
YEAR(date) |
|
23 |
EXTRACT(MONTH FROM date) |
Extract month from date |
MONTH(date) |
|
24 |
EXTRACT(DAY FROM date) |
Extract day from date |
DAY(date) |
|
25 |
EXTRACT(HOUR FROM time) |
Extract hour from time |
HOUR(time) |
|
26 |
EXTRACT(MINUTE FROM time) |
Extract minute from time |
MINUTE(time) |
|
27 |
EXTRACT(SECOND FROM time) |
Extract second from time |
SECOND(time) |
|
28 |
FLOOR(num) |
Get the largest preceding integer |
FLOOR(num) |
|
29 |
GREATEST(exp, exp2, …) |
Get the maximum value in a set |
GREATEST(exp, exp2, …) |
|
30 |
INITCAP(string) |
Capitalize words |
User-defined function |
|
31 |
INSTR(str, substr) |
Get position of substring |
INSTR(str, substr) |
|
INSTR(str, substr, pos) |
LOCATE(str, substr, pos) |
||
|
INSTR(str, substr, pos, num) |
User-defined function |
||
|
32 |
LAST_DAY(date) |
Get last day of the month |
LAST_DAY(date) |
|
33 |
LEAST(exp, exp2, …) |
Get the minimum value in a set |
LEAST(exp, exp2, …) |
|
34 |
LENGTH(string) |
Get length of string in chars |
CHAR_LENGTH(string) |
|
35 |
LENGTHB(string) |
Get length of string in bytes |
LENGTH(string) |
|
36 |
LN(num) |
Get natural logarithm of num |
LN(num) |
|
37 |
LOCALTIMESTAMP |
Get the current date and time |
LOCALTIMESTAMP |
|
LOCALTIMESTAMP([prec]) |
LOCALTIMESTAMP() |
||
|
38 |
LOG(num1, num2) |
Get logarithm, base num1, of num2 |
LOG(num1, num2) |
|
39 |
LOWER(string) |
Lowercase string |
LOWER(string) |
|
40 |
LPAD(string, len) |
Pad the left-side of string |
LPAD(string, len, ' ') |
|
LPAD(string, len, pad) |
LPAD(string, len, pad) |
||
|
41 |
LTRIM(string) |
Remove leading spaces |
LTRIM(string) |
|
LTRIM(string, set) |
Remove leading chars |
TRIM(LEADING set FROM string) |
|
|
42 |
MONTHS_BETWEEN(date1, date2) |
Get number of
months between |
User-defined function |
|
43 |
MOD(dividend, divisor) |
Get remainder |
MOD(dividend, divisor) |
|
44 |
Get the next date by day name |
NEXT_DAY user-defined function |
|
|
45 |
NULLIF(exp1, exp2) |
Return NULL if exp1 = exp2 |
NULLIF(exp1, exp2) |
|
46 |
NVL(exp, replacement) |
Replace NULL with the specified value |
IFNULL(exp, replacement) |
|
47 |
NVL2(exp1, exp2, exp3) |
Return exp2 if exp1 is
not NULL, |
CASE expression |
|
48 |
POWER(value, n) |
Raise value to the nth power |
POWER(value, n) |
|
49 |
REMAINDER(n1, n2) |
Get remainder |
(n1 - n2*ROUND(n1/n2)) |
|
50 |
REPLACE(str, search) |
Remove search-string |
REPLACE(str, search, '') |
|
REPLACE(str, search, replace) |
Replace search-string |
REPLACE(str, search, replace) |
|
|
51 |
ROUND(num, integer) |
Get rounded value |
ROUND(num, integer) |
|
52 |
RPAD(string, len) |
Pad the right-side of string |
RPAD(string, len, ' ') |
|
RPAD(string, len, pad) |
RPAD(string, len, pad) |
||
|
53 |
RTRIM(string) |
Remove trailing spaces |
RTRIM(string) |
|
RTRIM(string, set) |
Remove trailing chars |
TRIM(TRAILING set FROM string) |
|
|
54 |
SIGN(exp) |
Get sign of exp |
SIGN(exp) |
|
55 |
SIN(num) |
Get sine |
SIN(num) |
|
56 |
SINH(num) |
Get hyperbolic sine |
(EXP(num) - EXP(-num)) / 2 |
|
57 |
SOUNDEX(string) |
Get 4-character sound code |
SOUNDEX(string) |
|
58 |
SQRT(num) |
Get square root |
SQRT(num) |
|
59 |
SUBSTR(string, pos, len) |
Get a substring of string |
SUBSTR(string, pos, len) |
|
60 |
SYS_GUID() |
Get GUID, 32 characters without dashes |
REPLACE(UUID(), '-', '') |
|
61 |
SYSDATE |
Get current date and time |
SYSDATE() |
|
62 |
SYSTIMESTAMP |
Get current timestamp |
CURRENT_TIMESTAMP |
|
63 |
TAN(num) |
Get tangent |
TAN(num) |
|
64 |
TANH(num) |
Get hyperbolic tangent |
(EXP(2*num) - 1)/(EXP(2*num) + 1) |
|
65 |
Convert datetime to string |
||
|
Convert number to string |
|||
|
66 |
Convert string to datetime |
||
|
67 |
TO_LOB(exp) |
Convert to LOB |
|
|
68 |
TO_NCHAR(exp) |
Convert to NCHAR |
|
|
69 |
TO_NCLOB(exp) |
Convert to NCLOB |
|
|
70 |
TO_NUMBER(exp) |
Convert to NUMBER |
|
|
71 |
TO_SINGLE_BYTE(exp) |
Convert to single-byte character |
|
|
72 |
TO_TIMESTAMP(exp) |
Convert to TIMESTAMP |
|
|
73 |
TRANSLATE(string, from, to) |
Replace characters |
User-defined function |
|
74 |
TRIM([type trim FROM] string) |
Remove characters |
TRIM([type trim FROM] string) |
|
75 |
TRUNC(num) |
Truncate num |
TRUNCATE(num, 0) |
|
TRUNC(num, num2) |
TRUNCATE(num, num2) |
||
|
76 |
TRUNC(datetime) |
Truncate datetime |
|
|
77 |
UNISTR(string) |
Convert Unicode code points to chars |
CHAR(string USING UCS2) |
|
78 |
UPPER(string) |
Uppercase string |
UPPER(string) |
|
79 |
USER |
Get the current user |
USER() |
|
80 |
USERENV('parameter') |
Get the current session information |
|
|
81 |
VSIZE(exp) |
Get the size of exp in bytes |
|
|
82 |
XMLAGG(exp) |
Get a aggregated XML document |
|
|
83 |
XMLCAST(exp AS datatype) |
Convert exp to datatype |
|
|
84 |
XMLCDATA(exp) |
Generate a CDATA section |
|
|
85 |
XMLCOMMENT(exp) |
Generate an XML comment |
|
|
86 |
XMLCONCAT(exp, exp2, …) |
Concatenate XML expressions |
|
|
87 |
XMLDIFF(doc, doc2) |
Compare two XML documents |
|
|
88 |
XMLELEMENT(NAME element) |
Get an XQuery element node |
|
|
89 |
XMLFOREST(exp, exp2, …) |
Get a forest of XML expressions |
|
|
90 |
XMLISVALID(exp) |
Check XML exp |
|
|
91 |
XMLPARSE(DOCUMENT exp) |
Parse XML document |
|
|
92 |
XMLPATCH(doc, doc2) |
Patch XML document |
|
|
93 |
XMLPI(NAME identifier) |
Get XML processing instruction |
|
|
94 |
XMLROOT(exp, VERSION exp2) |
Create a new XML value |
|
|
95 |
XMLSEQUENCE(exp) |
Get a varray of the top-level nodes |
|
|
96 |
XMLSERIALIZE(CONTENT exp |
Get a serialized XML value |
|
|
97 |
XMLTRANSFORM(instance, exp) |
Transform XML document |
|
6.3 MySQL库表设计规范
6.3.1 MySQL 总体设计规范
l 本规范主要针对OLTP类型业务
l MySQL表、字段的设计使用统一、简洁、明确的原则设计
l 反范式设计逻辑
n 不需要强制满足数据库设计中的第三范式、尽量不使用外键约束
n 关联表的数据完整性可在业务端实现
n 表字段适度的冗余，减少多表JOIN的查询语句
l 保持MySQL数据库仅作为一个纯粹的数据存储系统，业务逻辑均在代码层实现
n 尽量避免使用自定义函数、存储过程、触发器、视图以便减少与业务耦合性
l 数据库的账户权限授予确保精确细化
n 严格遵循最小权限原则
n 应用用户避免授予DDL权限
n 不使用简单密码
6.3.2 MySQL 表结构设计规范
l 所有表名及字段名全部使用小写字母、数字与下划线组合
l 表名用有实际含义的英语单词，除约定俗成的英文缩写外，尽量避免使用缩写。
l 所有表及字段需要明确标注中文注释
l 所有表均使用InnoDB存储引擎且在建表语句中明确指定
n 完整的ACID事务支持
n 崩溃自动恢复能力
n 行级别锁，更高并发
l 所有表均使用UTF8或UTF8MB4字符集
l 所有表均需要有主键且建议用与业务无关的自增ID(保证ID列不会被修改)
l 所有表名及字段名尽量不超过32个字符 (保持与其他数据库兼容性)
l 所有表名及字段名不要使用MySQL的保留字和关键字(附录A)
l 不在数据库中存图片、二进制大文件
l 提前规划好表的容量、大表做好数据归档策略
6.3.3 MySQL 字段设计规范
l 字段尽量添加NOT NULL属性并设置默认值
l 字段存储非负数值时增加UNSIGNED属性
l 字段数据包含小数时尽量使用decimal类型而不使用浮点型
l 字段长度遵循最合适、最完整、最简短原则
n 最合适：选择最合适的数据类型存储不同数据，避免都使用VARCHAR
n 最完整：确保定义的数据类型长度可以存储完整的业务数据不会截断
n 最简短：确保能用占用字节更少的数据类型存储完整数据
l 不同表之间关联字段的类型、长度需保持一致
l 单表字段VARCHAR类型不超过16000(UTF8MB4)、21000(UTF8)
l 对于BLOB、TEXT等大字段字段,建议单独拆分一个表,只存放主表ID及BLOB或TEXT字段。
l 禁止使用BIT数据类型
l 不使用字段存储明文密码，密码必须加密存储
l 每张表包含CREATE_DATE、MODIFY_DATE字段
n CREATE_TIME用NOT NULL DEFAULT CURRENT_TIMESTAMP属性
MODIFY_DATE用NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP属性
6.3.4 MySQL 索引设计规范
l 要创建索引的字段需要选择性(Cardinality)较强
n 字段值需要唯一性较高，如主键的唯一性为100%
n 如：性别列不适合创建单列索引
l 索引长度不可超过768个字符(包括组合索引，utf8数据类型不可超过1024)
l 单表的索引在满足业务需求下尽可能控制在5个以内
l 对长度大于50的VARCHAR字段建立索引时,建立一定长度的前缀索引
l 合理的创建联合索引,联合索引(a,b,c) 等于 (a) 、(a,b) 、(a,b,c)三个索引
6.3.5 MySQL保留字和关键字
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
浙公网安备 33010602011771号