MySQL大数据表查询和删除重复的记录数据

MySQL大数据表查询和删除重复的记录数据

1）首先合成用于编写SQL语句的包含表的需要的列的字符串，逗号分割：

SELECT concat('select ',group_concat(
CASE 
WHEN COLUMN_NAME='id' 
THEN NULL 
ELSE 
concat('`',column_name ,'`')
END),' from ', TABLE_NAME,';') 
FROM information_schema.COLUMNS
WHERE table_schema='ax_crm_bk' 
AND table_name='datalist_idx';

2)查看表中的记录数据
select max(id) from datalist_idx;       #23974508
select count(1) from datalist_idx;      #23859438  #datalist:23884838

3）编写SQL语句

use ax_crm_bk;
delete from datalist_idx where id not in (
select t1.id from 
	(
		select min(id)
		from datalist_idx 
		group by `TaskID`,`KeyWord`,`UserCode`,`UserType`,`ComName`,`LinkMan`,`CollName`,`Postion`,`Phone`,`Mobile`,`Fax`,`Address`,`Post`,`Email`,`ComSite`,`ComInfo`,`MainProduce`,`MainHY`,`ComType`,`Businessmodel`,`Registeredcapital`,`Registeredaddress`,`Employeesnum`,`RegisteredDate`,`legalperson`,`GLTXRZ`,`SFTGOEMDGG`,`YFBMRS`,`CFMJ`,`GSZCXX`,`Turnover`,`MainMarket`,`SearchSite`,`SearchUrl`,`QQ`,`Skype`,`SearchDate`,`Province`,`City`,`IsFormat`,`IsEx`,`IsUpdate`,`Note1`,`Note2`,`Note3`,`Note4`,`Note5` 
	)t1
);

4）执行5分钟报错，解决报错：

Windows环境

ERROR 126 (HY000): Incorrect key file for table 'C:\Windows\TEMP\#sql1384_11_2.MYI'; try to repair it
对一个2500万数据的表做删除重复操作，引起了MYSQL对C:\Windows\TEMP文件夹下生成大量临时文件，
造成C盘空间不断耗尽，空间不足而报错。

1）查看tmpdir配置：
show variables like 'tmpdir';
2）修改MySQL的tmpdir的临时目录：
set global tmpdir = 'D:\ProgramData\MySQL\tmp';
执行发生错误：ERROR 1238 (HY000): Variable 'tmpdir' is a read only variable
原因：这个变量只能通过配置文件进行修改。
3）在my.ini中[mysqld]下增加：
tmpdir="D:\ProgramData\MySQL\tmp"
修改后MySQL启动报错：系统出错。发生系统错误 10,进程意外终止。
原因：这个系统临时目录D:\ProgramData\MySQL\tmp mySQL不会自己创建，要我们自己创建的，
一般的tmp目录的权限比较特别是drwxrwxrwt 就是1777，
表示任何人都可以在此目录拥有写权限但是不能删除别人拥有的文件。
经对比Windows下MySQL的默认临时目录C:\Windows\TEMP的文件权限，发现跟我们自己
创建的D:\ProgramData\MySQL\tmp目录不一样，于是将这个tmp目录的权限修改和Temp一样，但
特殊权限那个勾选不了，重启后仍然报错系统出错。发生系统错误 10,进程意外终止。
于是我把C:\Windows\TEMP中原来的文件全部删除，将其复制到D:\ProgramData\MySQL\Temp，
再在my.ini中[mysqld]下增加：
tmpdir="D:\ProgramData\MySQL\Temp"
重启mysql，进入CMD执行命令：
net stop mysql;
net start mysql;

启动成功，tmpdir成功修改到D:\ProgramData\MySQL\Temp，

        验证发现执行上述去重语句是D盘的剩余空间急剧消耗，如果剩余空间足够大的话，执行成功，

否则，在Linux上执行消耗硬盘剩余空间开始很慢，比较稳定，2小时后，又开始急剧消耗剩余空间直至耗尽，

以报错而告终：

[Err] 3 - Error writing file '/tmp/MYkZm79o' (Errcode: 28 - No space left on device)

                        

Linux环境下解决：

执行mySQL产生临时数据占满了/tmp磁盘空间的解决办法

转载  2016年08月17日 11:37:44

• 5548

mysql查询出现：mysql 126, "Incorrect key file for table '/tmp/#sql_597e_1.MYI'; try to repat it

原因是：执行mySQL产生临时数据占满了/tmp磁盘空间的解决办法

当mysql的临时文件大小设置太小，或者由于并发量太大导致临时文件存放的目录空间大小不够时都会报这个错误。”incorrect key file for the table ‘/tmp/#sql….';try to repair it”

一般的tmp目录的权限比较特别是drwxrwxrwt 就是1777
表示任何人都可以在此目录拥有写权限但是不能删除别人拥有的文件。

具体修改临时表目录的步骤为：

1) 查看法语站当前磁盘空间使用情况，建议将临时表存放目录设置在/var下。

【修改tmp目录】Incorrect <wbr>key <wbr>file <wbr>for <wbr>table <wbr>’/tmp/#sql_bd2_0.MYI’解决
[root@wholesale /]# mkdir /var/tmp
[root@wholesale /]# chmod 1777 /var/tmp/
2) my.cnf中[mysqld]下增加：

tmpdir=/var/tmp

3) 重启mysql服务
4) 查看mysql错误日志情况，看是否报错。
5) 进入mysql查看当前的tmpdir情况，确认是否修改完成
show variables like ‘tmpdir';