猫窝私语 — 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定时任务以及乱码处理

@玛酷猫11年前

08/6
13:30
Linux

redhat9下安装 MySQL5+Apache2+php5[转]

(正在研究LINUX,寻找了点资料,大家一起分享。)

准备工作: 下载安装文件mysql-standard-5.0.27-linux-i686.tar.gz、httpd-2.2.3.tar.gz、php-5.2.0.tar.gz ,并把它们放在/usr/local/src/ 文件夹里。
一、安装MySQL5
  
1、 在 /usr/local/  里建立 mysql 文件夹。
   # cd /usr/local
   # mkdir mysql
 
2、在 mysql 文件夹里解压文件( 这样解压出的文件夹就是当前文件夹下面 )。
   # cd mysql
   # tar -zxfz  /usr/local/src/mysql-standard-5.0.27-linux-i686.tar.gz
   # ln -s mysql mysql-standard-5.0.27-linux-i686 
//为文件夹 mysql-standard-5.0.27-linux-i686 建立快捷方式。
 
3、# groupadd mysql   
// 建立mysql组
   # useradd mysql -g mysql 
//建立mysql用户并且加入到mysql组中 
 
4、# cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf  
//在 support-files目录下有4个模版文件,我们选择其中一个座位Mysql的配置文件,覆盖/etc/my.cnf(系统默认的配置,其中设置了性能参数和Mysql的一些路径参数)。
 
5、# ./scripts/mysql_install_db –user=mysql    
//初试化表并且规定用mysql用户来访问。初始化表以后就开始给mysql和root用户设定访问权限。
 
6、# chown -R root .    
 //设定root能访问/usr/local/mysql ( 注意root 后面有一个空格和点)。
 
7、 # chown -R mysql data     
//设定mysql用户能访问/usr/local/mysql/data ,里面存的是mysql的数据库文件.这个目录是在/etc/my.cnf中有配置,在mysql_install_db时产生。
 
8、# chown -R mysql data/.    
//设定mysql用户能访问/usr/local/mysql/data/mysql下的所有文件
 
9、# chgrp -R mysql .      
//设定mysql组能够访问/usr/local/mysql
 
10、# /usr/local/mysql/bin/mysqld_safe –user=mysql &     
//运行mysql,如果没有问题的话,应该会出现类似这样的提示:[1] 42264
   # Starting mysqld daemon with databases from /usr/local/mysql/var    
//如果出现 mysql ended这样的语句,表示Mysql没有正常启动,你可以到log中查找问题,Log文件的通常在/etc/my.cnf中配置。大多数问题是权限设置不正确引起的。
 
11、# /usr/local/mysql/bin/mysqladmin -u root password yourpassword    
//默认安装密码为空,为了安全你必须马上修改.
 
12、# cp support-files/mysql.server /etc/rc.d/init.d/mysqld     
//copy编译目录的一个脚本
   # chmod 700 /etc/init.d/mysqld    
//设置使mysql每次启动都能自动运行
   # chkconfig –add mysqld
   # chkconfig –level 345 mysqld on
 
13、# service mysqld start    
//启动mysqld服务
   # netstat -atln     
//查看3306端口是否打开。要注意在防火墙中开放该端口。
  
二、安装apache2
  
1、解压文件
   # cd /usr/local 
   # tar -zxvf /usr/local/srchttpd-2.2.3.tar.gz
 
2、安装
   # cd httpd-2.2.3
   # ./configure –prefix=/usr/local/apache –enable-track-vars –enable-cgi –enable-so –enable-rewrite –enable-mods-shared=all –with-config-file-path=/usr/local/apache/conf
   # make
   # make install
 
3、启动
   # /usr/local/apache/bin/apachectl start
   # netstat -utl   //并检查是否启动
 
4、把apache加入开机启动,把下面这一行加入到/etc/rc.local中。
   /usr/local/apache/bin/apachectl start
  
三、安装php5
  
请先安装libxml,2.6.10以上版本的。

1、# cd /usr/local
   # tar -zvxf /usr/local/src/php-5.2.0.tar.gz 
   # cd php-5.2.0
 
2、# ./configure –prefix=/usr/local/php –with-mysql –with-apxs2=/usr/local/apache/bin/apxs
–enable-trace-vars –with-zlib-dir=/soft/zlib-1.2.3/
 
3、 # make; make install
 
4、拷贝PHP配置文件php.ini:
   # cp ../php5.2.0/php.ini-dist /usr/local/php/lib/php.ini
 
5、添加php类型
   # vi /usr/local/apache/conf/httpd.conf
   AddType application/x-httpd-php .php (230行左右)
 
6、重启
   # /usr/local/apache/bin/apachectl stop
   # /usr/local/apache/bin/apachectl start
 
7、测试
   # cd /usr/local/apache/htdocs
   # vi test.php
   <?php
      phpinfo();
   ?>
最后输入 http://您的地址/test.php 测试

redhat9下安装 MySQL5+Apache2+php5[转]