全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )

全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(实用技能)

准备工作,可以去下载 classicmodels 数据库资源如下

[ 点击:classicmodels]


(https://download.csdn.net/download/tomxjc/88685970)

前言

有没有在工作中碰到这样的数据场景:


由于前端技术构架的改变,前端订单系统已经很久没有人维护了,现出现了很多bug。所以需要改造,将原来的 .net 技术构架 改为java,计划用一个月的时间,2个系统并行,并最终切换。请数据部配合验证和核对数据数据。

我们假设 2个表的数据结构一样。那怎么验证呢具体如下:


一、数据准备

原来的表结构
CREATE TABLE `orderdetails` (
  `orderNumber` int NOT NULL,
  `productCode` varchar(15) NOT NULL,
  `quantityOrdered` int NOT NULL,
  `priceEach` decimal(10,2) NOT NULL,
  `orderLineNumber` smallint NOT NULL,
  PRIMARY KEY (`orderNumber`,`productCode`),
  KEY `productCode` (`productCode`),
  CONSTRAINT `orderdetails_ibfk_1` FOREIGN KEY (`orderNumber`) REFERENCES `orders` (`orderNumber`),
  CONSTRAINT `orderdetails_ibfk_2` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


新建的表结构
CREATE TABLE `orderdetails_test` (
  `orderNumber` int NOT NULL,
  `productCode` varchar(15) NOT NULL,
  `quantityOrdered` int NOT NULL,
  `priceEach` decimal(10,2) NOT NULL,
  `orderLineNumber` smallint NOT NULL,
  PRIMARY KEY (`orderNumber`,`productCode`),
  KEY `productCode` (`productCode`),
  CONSTRAINT `orderdetails_ibfk_3` FOREIGN KEY (`orderNumber`) REFERENCES `orders` (`orderNumber`),
  CONSTRAINT `orderdetails_ibfk_4` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

改为 orderdetails_ibfk_3,orderdetails_ibfk_4

CONSTRAINT orderdetails_ibfk_3 FOREIGN KEY (orderNumber) REFERENCES orders (orderNumber),

CONSTRAINT orderdetails_ibfk_4 FOREIGN KEY (productCode) REFERENCES products (productCode)

插入表 
insert into classicmodels.orderdetails_test
select * from classicmodels.orderdetails b 

插入数据

然后我们 假设 orderdetails_test有2个数据有异常我们更新一下

update orderdetails_test set priceEach='102.00' 
where priceEach='102.04' and productCode='S18_1589' and orderNumber='10161'

update orderdetails_test set priceEach='83.87' 
where priceEach='83.86' and productCode='S24_3816' and orderNumber='10273'

update orderdetails_test set orderLineNumber='1' 
where priceEach='136.00' and productCode='S18_1749' and orderNumber='10100' 


好了到这里就结束了,假设我们不知道数据有异常。

二、数据核对

1.检查表结构是否一致

代码如下(示例):

使用DESCRIBE语句可以查看表结构的
desc classicmodels.orderdetails 
desc classicmodels.orderdetails_test

发现表结构一致

2.检查记录条数是否一致

代码如下(示例):

SELECT COUNT(*) FROM classicmodels.orderdetails
SELECT COUNT(*) FROM classicmodels.orderdetails_test

发现行数一致

3.检查数据是否一致

通过 orderNumber 字段关联确认数据是否一致

SELECT * FROM orderdetails_test WHERE 
NOT EXISTS (SELECT * FROM orderdetails 
WHERE orderdetails.orderNumber=orderdetails_test.orderNumber)

通过 ProductCode 字段关联确认数据是否一致

SELECT * FROM orderdetails_test WHERE 
NOT EXISTS (SELECT * FROM orderdetails 
WHERE orderdetails.ProductCode=orderdetails_test.ProductCode)
没有记录,说明 数据一致


通过 quantityOrdered 字段关联确认数据是否一致

SELECT * FROM orderdetails_test WHERE 
NOT EXISTS (SELECT * FROM orderdetails 
WHERE orderdetails.quantityOrdered =orderdetails_test.quantityOrdered)
没有记录,说明数据一致

通过 priceEach 字段关联确认数据是否一致

SELECT * FROM orderdetails_test WHERE 
NOT EXISTS (SELECT * FROM orderdetails 
WHERE orderdetails.priceEach =orderdetails_test.priceEach)
有记录,说明数据不一致,ok 找到一条


到这里大家觉有问题吗?怎么少一条。是sql 写的不严谨?

4.检查数据是否一致 第2种方案 (CRC32 函数核对)


mysql crc32 函数

具体可以看下mysql 帮助

Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is NULL if the argument is NULL. The argument is expected to be a string and (if possible) is treated as one if it is not.

检查2个表列是否一致

select '原系统表' as 'titel',sum(CRC32(ordernumber)),
sum(CRC32(productCode)),
sum(CRC32(quantityOrdered)),
sum(CRC32(priceEach)),
sum(CRC32(orderLineNumber) )
from classicmodels.orderdetails
union all 
select '新系统表' as 'titel',sum(CRC32(ordernumber)),
sum(CRC32(productCode)),
sum(CRC32(quantityOrdered)),
sum(CRC32(priceEach)),
sum(CRC32(orderLineNumber) )
from classicmodels.orderdetails_test 


这样立刻就知道有2列 priceEach和orderLineNumber 数据和原表一样了

从列数据定位到行数据

select a.*  from (
select  ordernumber,productCode,priceEach,quantityOrdered,orderLineNumber,
CRC32(priceEach)+CRC32(orderLineNumber)+CRC32(productCode)+CRC32(quantityOrdered)+CRC32(ordernumber)   as num
from classicmodels.orderdetails_test ) a
left join 
(select  priceEach,orderLineNumber,
CRC32(priceEach)+CRC32(orderLineNumber)+CRC32(productCode)+CRC32(quantityOrdered)+CRC32(ordernumber) as num 
from classicmodels.orderdetails  ) b
on a.num=b.num
where b.num is  null

这样就快速找到了

注意CRC32 对 空格,回车也可以识别额

select CRC32(' ')

文本对比

select crc32('我爱北京天安门,天安门上太阳升,') as 'txt'
union all
select crc32('我爱北京天安门,天安门上太阳升, ') as 'txt'
union all
select crc32('我爱北京天安门,天安门上太阳升,
') as 'txt'
union all 
select crc32('我爱北京天安门,天安门上太阳升,') as 'txt'

5.其他方法

那之前的方案就没有办法办法实现是了吗?

有办法

SELECT * FROM orderdetails_test WHERE 
NOT EXISTS (SELECT * FROM orderdetails 
WHERE orderdetails.priceEach =orderdetails_test.priceEach 
and orderdetails.orderLineNumber =orderdetails_test.orderLineNumber
and orderdetails.ordernumber =orderdetails_test.ordernumber 
and orderdetails.productCode =orderdetails_test.productCode
and orderdetails.productCode =orderdetails_test.productCode)

总结

这个2个方法都可行,但是用 CRC32 函数在文本对文本对比 效率会比较高 。希望大家学到了,这个是之前工作的一些笔记。



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
安全 关系型数据库 MySQL
如何将数据从MySQL同步到其他系统
【10月更文挑战第17天】如何将数据从MySQL同步到其他系统
209 0
|
26天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
131 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
26天前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
在项目中,为了解决Redis与Mysql的数据一致性问题,我们采用了多种策略:对于低一致性要求的数据,不做特别处理;时效性数据通过设置缓存过期时间来减少不一致风险;高一致性但时效性要求不高的数据,利用MQ异步同步确保最终一致性;而对一致性和时效性都有高要求的数据,则采用分布式事务(如Seata TCC模式)来保障。
58 14
|
29天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
53 9
|
2月前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
1月前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
47 1
|
1月前
|
SQL 关系型数据库 MySQL
mysql数据误删后的数据回滚
【11月更文挑战第1天】本文介绍了四种恢复误删数据的方法:1. 使用事务回滚,通过 `pymysql` 库在 Python 中实现;2. 使用备份恢复,通过 `mysqldump` 命令备份和恢复数据;3. 使用二进制日志恢复,通过 `mysqlbinlog` 工具恢复特定位置的事件;4. 使用延迟复制从副本恢复,通过停止和重启从库复制来恢复数据。每种方法都有详细的步骤和示例代码。
209 2
|
27天前
|
关系型数据库 MySQL 数据库
【赵渝强老师】启动与关闭MySQL数据库实例
MySQL数据库安装完成后,可以通过命令脚本启动、查看状态、配置开机自启、查看自启列表及关闭数据库。本文提供了详细的操作步骤和示例代码,并附有视频讲解。
|
2月前
|
存储 关系型数据库 MySQL
mysql 8.0 的 建表 和八种 建表引擎实例
mysql 8.0 的 建表 和八种 建表引擎实例
26 0
|
2月前
|
存储 关系型数据库 MySQL
面试官:MySQL一次到底插入多少条数据合适啊?
本文探讨了数据库插入操作的基础知识、批量插入的优势与挑战,以及如何确定合适的插入数据量。通过面试对话的形式,详细解析了单条插入与批量插入的区别,磁盘I/O、内存使用、事务大小和锁策略等关键因素。最后,结合MyBatis框架,提供了实际应用中的批量插入策略和优化建议。希望读者不仅能掌握技术细节,还能理解背后的原理,从而更好地优化数据库性能。