猫窝私语 — Makumo's Blog

玛酷猫的温馨小窝,记录生活点点滴滴。

@玛酷猫2年前

02/24
21:01
数据库

MySQL同一表中重复数据处理

PS:工作中的随手记录,并非最佳处理方式,仅作为一种思路。

筹备新系统的过程中,遇到这样一个情况,数据库中一个表已经添加了300条信息,这时候突然从外部直接导入了1000条信息,其中有一部分重复信息,在重复的信息里面,新加入的数据有一个字段数据是原来数据里面没有的。现在需要将重复数据中原来没有的那个字段数据更新到原始数据中,并且删除重复数据。假设数据表结构简约如下:

表A
id name phone
1 张三
2 李四
3 王五
-----------------------------
1000 李四 1234567890
1001 赵六 0987654321

其中虚线以上部分是原有数据,以下部分是批量导入数据,phone字段仅新插入数据有内容。

由于在一个表中,先查下有多少重复数据,SQL如下

select name,count(name) as num from 表A group by name having num>1

有一点要注意就是在group by的语句中where和having的区别,两者处理数据顺序不同,where是在group by之前处理筛选数据,having则是group by之后在处理筛选数据,其中重复的数据大概有接近200条。

下一步就是把重复的这近200条数据中,新加入数据中那一个特殊字段的数据更新到老数据中。思路为先从上面重复数据里面分离出后添加的数据,将后添加数据特殊字段内容更新到前面老数据中,假设后添加数据起始id为1000。

update 表A as aa 
inner join (select a.id,a.name,a.phone from 表A as a,(select name,count(name) as num from 表A group by name having num>1) as b where a.name = b.name and a.id >= 1000) as bb
on aa.name = bb.name
set aa.phone = bb.phone
where aa.id < 1000

这里不知道是不是mysql(5.6)版本问题还是其他原因,之前使用update……set……from……select……where语句来更新数据的时候报错,后来换成上述语句更新。

更新完了后就是删除重复数据了,主要还是删除后面添加进来的数据。

delete from 表A 
where id in (
  select aa.id from (
    select a.id from 表A as a,
      (select name,count(name) as num from 表A group by name having num>1) as b 
      where a.name = b.name and a.id >= 1000
    ) aa
  )

这里其实多套了一层select,由于mysql中不允许在同一个表中先select出数据在update这个表,会报类似这样的错误 You can’t specify target table for update in FROM clause,中间多加了一层select来规避这个错误的出现。

至此问题处理完毕。其实在一个表中做这样的处理是非常不好的方法,在数据导入的时候先导入一个临时表中,在进行两个表之间的数据处理,从逻辑上和语句上面都简单很多,出现意外错误的几率也会少很多,误操作影响原有数据几率也小很多。

MySQL同一表中重复数据处理

@玛酷猫6年前

03/12
14:12
数据库

mysql定时任务以及乱码处理

前几天接到一个工作需求,在OA中增加一个功能,每天给当天过生日的同事发送一封生日快乐的邮件,并发送站内短信给所有人,提醒今天某某过生日。这个需求不错,很能体现人文关怀,如是乎构思如何实现。

先说下背景情况,OA是完全自行开发的,CI框架,WIMP架构(最初建设的时候由于各种原因,等接手的时候已经是这种别扭的架构)。最一开始准备用windows的任务计划。制作一个单页实现功能,在定时任务里面设置执行一个vbs文件,访问下该文件。内容类似如下

DIM IE
SET IE = CREATEOBJECT("INTERNETEXPLORER.APPLICATION")
IE.NAVIGATE("网址")
IE.VISIBLE=0
SET IE = NOTHING

后来考虑这一系列都是数据路操作,没必要绕一大圈,又写php程序又在windows下面建计划任务,mysql已经支持计划任务。于是乎就准备在mysql里面建一个存储过程一个计划事件搞定它。定时任务需要mysql版本在5.1以上,版本不够的话可以用上面的方式。

MYSQL的存储过程还是第一次写,以前只在MSSQL里面写过存储过程和触发器。不停地查找写法资料才完成如下代码(初次写难免存在问题,欢迎指正),

#这个是最原始版本,实际应用版本做了修改优化#
DROP PROCEDURE IF EXISTS `pro_birth`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_birth`()
BEGIN
	DECLARE t_uid INT;
	DECLARE a_uid INT;
	DECLARE t_username VARCHAR(255);
	DECLARE t_depname VARCHAR(255);
	DECLARE done INT DEFAULT 0;

	DECLARE cur1 CURSOR FOR 当日过生日人员名单SQL;
	DECLARE cur2 CURSOR FOR 全员名单SQL;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	OPEN cur1;
	REPEAT
	FETCH cur1 INTO t_uid,t_username,t_depname;
	IF NOT done THEN

		#生日邮件
		SET @TEMP_STRING = CONCAT("'<div style=font-size:14px><p>亲爱的",t_username,":</p><p>  祝您生日快乐!</p><p>  感谢您为公司发展付出的辛勤劳动和努力。在您生日到来之际,公司代表全体员工送上衷心的祝福:生日快乐!愿所有的快乐、所有的幸福、所有的温馨、所有的好运都永远围绕在你的身边。</p><p style=text-align:right>",DATE_FORMAT(CURDATE(),'%Y年%m月%d日'),"</p></div>' ");
		SET @MAIL_STRING = "插入邮件表SQL";
		PREPARE mail_stmt FROM @MAIL_STRING;
		EXECUTE mail_stmt;

		#全员站内短信提醒
		OPEN cur2;
		REPEAT
		FETCH cur2 INTO a_uid;
		IF NOT done THEN
			SET @TEMP_STRING = CONCAT("亲爱的伙伴:今天(",DATE_FORMAT(CURDATE(),'%m月%d日'),")是",t_depname," ",t_username,"的生日,快去给TA送祝福吧!");
			SET @PM_STRING = "插入短信表SQL"
			PREPARE pm_stmt FROM @PM_STRING;
			EXECUTE pm_stmt;
		END IF;
		UNTIL done END REPEAT;
		CLOSE cur2;

	END IF;
	UNTIL done END REPEAT;
	CLOSE cur1;
END
;;
DELIMITER ;

本机测试正常,在服务器上创建存储过程的时候却报错。“#1271 – Illegal mix of collations for operation ‘concat’”,谷歌了一下,都说是编码问题导致。检查了下服务器上牵扯到得几个表,用户表、邮件表、短信息表都是utf8的编码。心想是不是存储过程中间的中文导致报错,于是重新改写了下代码,新建一个临时表,将存储过程中的文字存放在库中,读取出来替换掉中间的关键字。代码类似,就不在重复贴了。服务器重新创建存储过程的时候执行成功,但是运行存储过程后,插入邮件表和短信息表的内容还是乱码,中文部分全都是用?代替。看来还是没有解决中文乱码的问题。

多番网上查找后,有位同仁也遇到此类问题,他的表和库都是utf8的编码,同样存储过程运行后中文都是??,这时候我才发现我这服务器上库的编码居然是latin1_swedish_ci,存储过程肯定也是这个编码,虽然表的数据都是utf8的,经过存储过程一弄,就全变??了。解决办法其实也很简单,变量声明的时候定一下编码格式。

DECLARE t_username VARCHAR(255) CHARACTER SET utf8;
DECLARE t_depname VARCHAR(255) CHARACTER SET utf8;

这个也不是彻底的解决办法,最终需要统一库的编码格式。

定时事件就简单多了,新建一个事件,每天凌晨6点运行下就好了。要核对下mysql的系统时间,像我的是+8区就不用调整了,如果默认的话需要对应做个加减。如下

DROP EVENT IF EXISTS `EVENT_BIRTH`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` EVENT `EVENT_BIRTH` ON SCHEDULE EVERY 1 DAY STARTS '2012-03-08 6:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL pro_birth()
;;
DELIMITER ;

最后,最关键一点,开启mysql的定时事件。我就是忘开启了,结果等了一天没动静。。。今天的手动执行了下存储过程。

set global event_scheduler = 1

mysql定时任务以及乱码处理

@玛酷猫10年前

11/12
15:06
数据库

优化SQL Server的内存占用之执行缓存

(转载以便日后查找,原作者实在没找到是谁,不过还是很感谢作者)

在论坛上常见有朋友抱怨,说SQL Server太吃内存了。这里笔者根据经验简单介绍一下内存相关的调优知识。首先说明一下SQL Server内存占用由哪几部分组成。SQL Server占用的内存主要由三部分组成:数据缓存(Data Buffer)、执行缓存(Procedure Cache)、以及SQL Server引擎程序。SQL Server引擎程序所占用缓存一般相对变化不大,则我们进行内存调优的主要着眼点在数据缓存和执行缓存的控制上。本文主要介绍一下执行缓存的调优。数据缓存的调优将在另外的文章中介绍。

对于减少执行缓存的占用,主要可以通过使用参数化查询减少内存占用。

1、使用参数化查询减少执行缓存占用

我们通过如下例子来说明一下使用参数化查询对缓存占用的影响。为方便试验,我们使用了一台没有其它负载的SQL Server进行如下实验。下面的脚本循环执行一个简单的查询,共执行10000次。
  首先,我们清空一下SQL Server已经占用的缓存:

dbcc freeproccache

  然后,执行脚本:

DECLARE @t datetime
SET @t = getdate()
SET NOCOUNT ON
DECLARE @i INT, @count INT, @sql nvarchar(4000)
SET @i = 20000
WHILE @i <= 30000
BEGIN
    SET @sql = 'SELECT @count=count(*) FROM P_Order WHERE MobileNo = ' + cast( @i as varchar(10) )
    EXEC sp_executesql @sql ,N'@count INT OUTPUT', @count OUTPUT
    SET @i = @i + 1
END
PRINT DATEDIFF( second, @t, current_timestamp )

输出:
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
11

使用了11秒完成10000次查询。

我们看一下SQL Server缓存中所占用的查询计划:

Select Count(*) CNT,sum(size_in_bytes) TotalSize From sys.dm_exec_cached_plans

查询结果:共有2628条执行计划缓存在SQL Server中。它们所占用的缓存达到:
92172288字节 = 90012KB = 87 MB。
Read More →

优化SQL Server的内存占用之执行缓存