1. 数据类型基础概念解析刚接触数据库时我最头疼的就是各种数据类型的定义。比如为什么有的数字要定义成NUMBER(10,2)有的却用INTVARCHAR和CHAR到底有什么区别这些概念直接关系到数据存储的准确性和空间利用率。长度、精度、标度这三个概念是理解数据类型的核心长度指字段占用的存储空间。比如INT类型固定占用4字节VARCHAR(50)表示最多占用50个字符的存储空间注意字符集影响实际字节数精度针对数字类型指数字的总位数。如NUMBER(5,2)中的5表示最多存储5位数字标度数字类型中小数点后的位数。NUMBER(5,2)中的2表示保留2位小数实际案例在财务系统中金额字段通常会定义为DECIMAL(19,4)-- MySQL CREATE TABLE financial_trans ( amount DECIMAL(19,4) -- 最大15位整数4位小数 ); -- Oracle CREATE TABLE financial_trans ( amount NUMBER(19,4) );2. 三大数据库类型对比2.1 数值类型差异在数据迁移时数值类型是最容易出问题的。去年我们有个项目从Oracle迁移到MySQL就遇到了精度溢出的问题。Oracle的NUMBER类型是个万能类型不指定精度时可存储任意精度的数字实际存储空间根据值的大小动态变化对应MySQL的DECIMAL但行为不完全相同MySQL的数值类型更细分TINYINT -- 1字节 (-128~127) SMALLINT -- 2字节 (-32768~32767) INT -- 4字节 (-21亿~21亿) BIGINT -- 8字节 DECIMAL -- 精确小数 FLOAT -- 4字节浮点 DOUBLE -- 8字节浮点SQL Server的特别之处MONEY -- 货币类型 SMALLMONEY -- 小货币迁移建议Oracle NUMBER → MySQL DECIMALOracle INTEGER → MySQL INTSQL Server MONEY → MySQL DECIMAL(19,4)2.2 字符串类型陷阱字符串类型的差异主要体现在三个方面长度语义、空格处理和字符集支持。VARCHAR的坑Oracle中VARCHAR2最大4000字节MySQL 5.0的VARCHAR最大65535字节实际受行大小限制SQL Server的VARCHAR(MAX)可存2GB数据字符集的影响以UTF-8为例-- MySQL CREATE TABLE user_profile ( name VARCHAR(50) CHARACTER SET utf8mb4 -- 最多50个字符 ); -- Oracle CREATE TABLE user_profile ( name VARCHAR2(150 BYTE) -- 按字节计算中文通常需要3字节 );实际踩坑案例我们曾将Oracle的VARCHAR2(100)直接映射到MySQL的VARCHAR(100)结果发现存储中文时MySQL能存更多字符导致业务逻辑出错。2.3 日期时间类型日期类型的精度差异经常被忽视类型Oracle支持范围MySQL支持范围SQL Server精度DATE公元前4712年~公元9999年1000-01-01~9999-12-311753-01-01~9999-12-31TIMESTAMP秒级精度微秒级精度(6位小数)100纳秒精度迁移脚本示例-- Oracle → MySQL日期转换 SELECT TO_CHAR(create_time, YYYY-MM-DD HH24:MI:SS) AS fmt_time FROM orders; -- MySQL中直接插入 INSERT INTO orders_mysql VALUES (STR_TO_DATE(2023-01-15 14:30:00, %Y-%m-%d %H:%i:%s));3. 跨库迁移实战方案3.1 自动化字段映射通过查询系统表获取元数据然后自动生成转换规则-- Oracle获取表结构 SELECT column_name, data_type, data_length, data_precision, data_scale FROM all_tab_columns WHERE table_name ORDERS; -- MySQL对应查询 SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM information_schema.columns WHERE table_name orders;我开发过一个类型转换工具类核心逻辑如下public String convertDataType(String sourceType, int length, int precision, int scale) { switch(sourceType.toUpperCase()) { case VARCHAR2: return VARCHAR( (length * 3) ); // 考虑UTF-8编码 case NUMBER: if(scale 0) return DECIMAL( precision , scale ); else if(precision 0) return DECIMAL(38); // Oracle的NUMBER不带参数 else return BIGINT; case DATE: return DATETIME; default: return sourceType; } }3.2 数据校验方案迁移后必须进行数据校验我常用的方法有计数校验比对源表和目标表的记录数-- Oracle SELECT COUNT(*) FROM source_table; -- MySQL SELECT COUNT(*) FROM target_table;抽样校验检查关键字段的一致性# 使用Python进行数据抽样比对 import cx_Oracle import pymysql # 连接数据库 ora_conn cx_Oracle.connect(user/passoracle) mysql_conn pymysql.connect(hostlocalhost, userroot) # 随机抽样100条记录 ora_cursor.execute(SELECT * FROM (SELECT * FROM orders ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM 100) mysql_cursor.execute(SELECT * FROM orders ORDER BY RAND() LIMIT 100) # 比对关键字段 for ora_row, mysql_row in zip(ora_cursor, mysql_cursor): assert abs(ora_row[amount] - mysql_row[amount]) 0.001哈希校验对大表进行快速一致性检查-- Oracle SELECT ORA_HASH(SUM(DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(column1||column2), 3))) FROM big_table; -- MySQL SELECT MD5(CONCAT(SUM(CRC32(CONCAT(column1, column2))))) FROM big_table;4. 常见问题解决方案4.1 数据截断问题场景VARCHAR长度不足导致数据被截断解决方案提前分析源数据实际长度-- 查询Oracle字段实际最大长度 SELECT MAX(LENGTH(column_name)) FROM table_name;目标字段设置合理长度考虑字符集对超长数据特殊处理如截断日志记录4.2 精度丢失问题案例Oracle的NUMBER直接转MySQL的FLOAT导致小数精度丢失正确做法-- Oracle的NUMBER(15,4)应该转为 DECIMAL(15,4) -- MySQL DECIMAL(15,4) -- SQL Server4.3 日期边界问题各数据库的日期范围不同迁移时需要特别注意-- 检查Oracle的越界日期 SELECT COUNT(*) FROM orders WHERE order_date TO_DATE(1000-01-01, YYYY-MM-DD); -- 转换策略 CASE WHEN order_date 1000-01-01 THEN 0999-12-31 ELSE order_date END5. 性能优化建议批量处理避免单条提交// 错误做法 for(Order order : orders) { jdbcTemplate.update(INSERT...); } // 正确做法 jdbcTemplate.batchUpdate(INSERT..., batchArgs);禁用约束和索引迁移完成后再重建-- MySQL ALTER TABLE target_table DISABLE KEYS; -- 迁移数据... ALTER TABLE target_table ENABLE KEYS;并行处理对大表按分区或主键范围并行迁移数据类型优化根据目标库特性调整-- Oracle的CLOB转MySQL的TEXT -- 但超长文本考虑分表或文件存储在实际项目中我们曾用这套方法将2TB的Oracle数据仓库迁移到MySQL集群最终数据差异率控制在0.001%以内。关键是要充分了解各数据库类型的特性提前做好映射规划并在迁移后进行严格校验。