千万级数据库表去重复的方法
20240124更新，MYSQL（有多个重复时只保留第一行）：
DELETE FROM my_table WHERE id NOT IN (SELECT id FROM (SELECT MIN(id) AS id FROM my_table GROUP BY fieldname123) AS a)
20210910更新，MYSQL（只能删除最后一行重复）：
DELETE FROM my_extend_weixin_fans WHERE wxid=123 AND id IN (SELECT id FROM (SELECT MAX(id) AS id FROM my_extend_weixin_fans WHERE wxid=123 GROUP BY openid HAVING COUNT(openid)>1)AS a)
CREATE TABLE mypwd
(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
pwd VARCHAR(14) NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE KEY pwd(pwd)
)ENGINE=MYISAM, CHARSET='utf8'
INSERT INTO mypwd(pwd) SELECT DISTINCT pwd FROM oldtable
采用这种方式，从已简单处理过的1300多W的表中复制出DISTINCT pwd 1200多W条到mypwd，用时11分39秒，E5400的CPU占用率低于60%，内存占用也很少。
如果不给pwd字段建索引，仅用110秒就复制完，再给pwd字段创建UNIQUE KEY用时9分34秒。
所以用这种方式还是不错的
/**************下面的方法待验证********************/
如果要删除手机(mobilePhone)，电话(officePhone)，邮件(email)同时都相同的数据，以前一直使用这条语句
delete from 表 where id not in (
select max(id) from 表 group by mobilePhone,officePhone,email )
or
delete from 表 where id not in (
select min(id) from 表 group by mobilePhone,officePhone,email )
下面的方法效率更高
效率对比：用delete方法对500万数据去重（1/2重复）约4小时
用临时表插入对500万数据去重（1/2重复）不到10分钟
//查询出唯一数据的ID,并把他们导入临时表tmp中
select min(id) as mid into tmp from 表 group by mobilePhone,officePhone,email
//查询出去重后的数据并插入finally表中
insert into finally select (除ID以外的字段) from customers_1 where id in (select mid from tmp)
两表组合更新示例
Update [INVOICE] SET INVOICE.BUREAU_NO=TP_USER.BUREAU_NO
FROM [INVOICE] JOIN [TP_USER] ON INVOICE.TAXPAYER_ID=TP_USER.TAXPAYER_ID
WHERE INVOICE.TAX_FLAG='正常' And INVOICE.TAX_DATE<>'1900-1-1'
以上不适用于access
MYSQL:
UPDATE customer SET customer.amount=(SELECT SUM(works_renewals.contractamount) FROM works_renewals WHERE customer.cid=works_renewals.cid)
UPDATE customer
SET customer.photo = COALESCE(
(SELECT works_renewals.photo FROM works_renewals
WHERE works_renewals.customid=customer.id),
'http://abc.com/photo.jpg' -- 当子查询返回 NULL 时使用这个默认值
);
MYSQL
UPDATE book SET price=100 WHERE id IN (SELECT id FROM book AS a WHERE a.price=99)
错误代码： 1093
You can't specify target table 'book' for update in FROM clause
mysql同表组合更新时，需要把SELECT出来的数据放到临时表中。SELECT的表和临时表都要有别名。正确语句：
UPDATE book SET price=100 WHERE id IN (SELECT b.id FROM (SELECT id FROM book AS a WHERE a.price=99) AS b)
MySQL自表更新示例
update
wxy_enum, (select tmp.id, tmp.addfield1 from wxy_enum as tmp) tmp2
set wxy_enum.parentid=tmp2.id
where wxy_enum.addfield2=tmp2.addfield1 and wxy_enum.rootid>0
当表A.name=表B.name时，用 A.id 填充 B.aid
UPDATE tableA
JOIN tableB ON tableA.name = tableB.name
SET tableB.aid = tableA.id;
UTF-8、UNICODE互转
<?php
//UTF-8转UNICODE
function utf8_unicode($s)
{
if (is_string($s)) {
$s = mb_str_split($s);
}
$arr = array();
$n = 0;
for ($i = 0; $i < count($s); $i++) {
switch (strlen($s[$i])) {
case 1:
$n = ord($s[$i]);
break;
case 2:
$n = (ord($s[$i][0]) & 63) << 6;
$n += ord($s[$i][1]) & 63;
break;
case 3:
$n = (ord($s[$i][0]) & 31) << 12;
$n += (ord($s[$i][1]) & 63) << 6;
$n += ord($s[$i][2]) & 63;
break;
case 4:
$n = (ord($s[$i][0]) & 15) << 18;
$n += (ord($s[$i][1]) & 63) << 12;
$n += (ord($s[$i][2]) & 63) << 6;
$n += ord($s[$i][3]) & 63;
break;
}
$arr[] = $n;
}
return $arr;
}
function mb_str_split($str, $charset = 'utf-8', $length = 1)
{
if ($length < 1) {
return FALSE;
}
$result = array();
for ($i = 0; $i < mb_strlen($str, $charset); $i += $length) {
$result[] = mb_substr($str, $i, $length, $charset);
}
return $result;
}
//UNICODE转UTF-8
function unicode_utf8($u)
{
if (is_int($u)) {
$u = array($u);
}
$arr = array();
$tmp = '';
for ($i = 0; $i < count($u); $i++) {
if ($u[$i] < 128) {
$tmp = $u[$i];
} else {
if ($u[$i] < 2048) {
$tmp = chr(192 | $u[$i] >> 6);
$tmp .= chr(128 | $u[$i] & 63);
} else {
if ($u[$i] < 65536) {
$tmp = chr(224 | $u[$i] >> 12);
$tmp .= chr(128 | $u[$i] >> 6 & 63);
$tmp .= chr(128 | $u[$i] & 63);
} else {
if ($u[$i] < 2097152) {
$tmp = chr(240 | $u[$i] >> 18);
$tmp .= chr(128 | $u[$i] >> 12 & 63);
$tmp .= chr(128 | $u[$i] >> 6 & 63);
$tmp .= chr(128 | $u[$i] & 63);
}
}
}
}
$arr[] = $tmp;
}
return $arr;
}
PHP生成静态页面
一种是利用模板技术，另一种是用ob系列函数。两种方法，看起来都差不多，但是实际上，却是不同的。
第一种：利用模板
目前PHP的模板可以说是很多了，有功能强大的smarty，还有简单易用的smarttemplate等。
它们每一种模板，都有一个获取输出内容的函数。我们生成静态页面的方法，就是利用了这个函数。用这个方法的优点是，代码比较清晰，可读性好。
这里我用smarty做例子，说明如何生成静态页
<?php
require('smarty/Smarty.class.php');
$t = new Smarty;
$t->assign("title","Hello World!");
$content = $t->fetch("templates/index.htm");
//这里的 fetch() 就是获取输出内容的函数,现在$content变量里面,就是要显示的内容了
$fp = fopen("archives/2005/05/19/0001.html", "wb");
fwrite($fp, $content);
fclose($fp);
?>
第二种方法：利用ob系列的函数
这里用到的函数主要是 ob_start(), ob_end_flush(), ob_get_content(),
其中ob_start()是打开浏览器缓冲区的意思，打开缓冲后，所有来自PHP程序的非文件头信息均不会发送，而是保存在内部缓冲区，直到你使用了ob_end_flush()。而这里最重要的一个函数，就是ob_get_contents()，这个函数的作用是获取缓冲区的内容，相当于上面的那个fetch()，道理一样的。代码：
<?php
ob_start();
$fp = fopen("ip.html", "wb");
fwrite($fp, ob_get_contents());//取得php页面输出的全部内容
fclose($fp);
ob_end_clean();
?>
PHP遍历文件件
<?php
$dir = '.';
//当前目录
list_file($dir);
function list_file($dir)
{
$list = scandir($dir);
// 得到该文件下的所有文件和文件夹
foreach ($list as $file) {
//遍历
$file_location = $dir . '/' . $file;
//生成路径
if (is_dir($file_location) && $file != '.' && $file != '..') {
//判断是不是文件夹
echo "------------------------sign in {$file_location}------------------";
echo '<br>';
list_file($file_location);
} else {
echo is_file($dir . '/' . $file) ? $dir . '/' . $file . '<br>' : '';
}
}
}如遇文件名显示乱码，可用mb_convert_encoding 或 iconv 转换
文本内容return array()的使用
a.txt或a.php内容：
return array(
0 => 'hello',
1 => 'world',
);
调用：
<?php
$arr = eval(file_get_contents('a.txt'));
?>
方法二：
a.php内容：
<?php
return array(
0 => 'hello',
1 => 'world',
);
?>
调用：
<?php
$arr = @include $path.'a.php';
?>
PHP保存远程文件到本地
echo httpcopy("http://www.baidu.com/img/baidu_sylogo1.gif");
function httpcopy($url, $file="", $timeout=60) {
$file = empty($file) ? pathinfo($url,PATHINFO_BASENAME) : $file;
$dir = pathinfo($file,PATHINFO_DIRNAME);
!is_dir($dir) && @mkdir($dir,0755,true);
$url = str_replace(" ","%20",$url);
if(function_exists('curl_init')) {
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_TIMEOUT, $timeout);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
$temp = curl_exec($ch);
if(!curl_error($ch)){
$dest = fopen($file, 'wb');
fwrite($dest, $temp);
fclose($dest);
return $file;
}else{
return false;
}
}elseif(function_exists('copy')) {
$opts = array(
"http"=>array(
"method"=>"GET",
"header"=>"",
"timeout"=>$timeout)
);
$context = stream_context_create($opts);
if(@copy($url, $file, $context)) {
//$http_response_header
return $file;
} else {
return false;
}
}else{
$temp=fopen($url,'rb');
if($temp){
$newf=fopen($file,'wb');
if($newf){
while(!feof($temp)){
fwrite($newf,fread($temp,1024*8),1024*8);
}
}
if($temp){
fclose($temp);
}
if($newf){
fclose($newf);
}
return $file;
}
}
}
简单的PHP采集示例
<?php
header('Content-type: text/html; charset=utf-8');
set_time_limit(900);
$a=<<<EOD
复制html内容到这里
EOD;
//获取需要的链接
preg_match_all('/\\/chapter\\/110701\\/\\d+\\.html/i', $a, $arr);
//得到完整链接地址
$arr = explode(',', 'http://vip.book.sina.com.cn' . implode(',http://vip.book.sina.com.cn', $arr[0]));
$b = '文章正文内容开始前面的html';
$c = '/正则匹配章节标题和章节内容之间的html，如果此部门内容没有时间等不同的字符，可以直接用str_replace而不是preg_replace';
$e = '文章正文内容结束后面的html';
$result = '';
//要获取的文章内容
$txt = fopen('e:/web/abc.txt', 'wb');
ob_start();
for ($i = 0; $i < count($arr); $i++) {
$result = '';
$str = file_get_contents($arr[$i]);
//也可以用curl
$str = explode($b, $str);
$str = explode($e, count($str) > 1 ? $str[1] : $str[0]);
$str = explode('</h1>', $str[0], 2);
$result = $str[0] . "\r\n";
$str = preg_replace($c, '', $str[1]);
$str = str_replace('<p>', '', $str);
$str = str_replace('</p>', "\r\n", $str);
$result .= $str;
fwrite($txt, $result);
echo str_replace("\r\n", '<br />', $result);
flush();
}
fclose($txt);
file_get_contents()抓取 https 地址时出错：
SSL operation failed with code 1. OpenSSL Error message
原因是证书校验不通过，可以设置忽略证书校验：
$option=array(
'ssl'=>array('verify_peer' => false, 'verify_peer_name' => false)
);
$stream=stream_context_create($option);
file_get_contents($url, false, $stream);
artDialog 网页对话框插件
PHP curl示例
cur进行https请求时，如果出现 curl: (35) SSL connect error
一般是发起请求的服务器ssl_version为 NSS 需要改为 openSSL
/**
* 通过curl请求远程数据
* @param string $url
* @param string $request_method 默认GET
* @param array $post_data 默认null
* @param string $do_what http_build_query(发送多组数组时必须使用此项)/json_encode
* @param array $option_array 批量设置特殊选项
* @param bool return_with_curlinfo 是否返回curlinfo信息
* 用下面的方式批量设置curl选项（选项有多种类型，参考：https://www.php.net/manual/zh/function.curl-setopt ）
* $option_array=array(
CURLOPT_HTTPHEADER=>array(
'Connection: keep-alive',
'Content-Type: application/json; charset=utf-8',
),
CURLOPT_TIMEOUT => 3,
);
* @return FALSE | string
*/
function my_curl($url, $request_method='GET', $post_data=NULL, $do_what='', $option_array=array(), $return_with_curlinfo=false){
is_url($url) OR $url=(is_https()?'https://':'http://').$_SERVER['HTTP_HOST'].$url;
is_array($option_array) OR $option_array=array();
if($post_data){
switch($do_what){
case 'json_encode': //需要发送json数据时，对数组或对象进行json编码（微信开发此处不能包含中文，否则会有：不合法的请求字符,不能包含\uxxxx格式的字符）
$post_data = json_encode($post_data);
$option_array[CURLOPT_HTTPHEADER][] = 'Content-length: '.strlen($post_data);
$option_array[CURLOPT_HTTPHEADER][] = 'Content-Type: application/json; charset=utf-8';
break;
case 'http_build_query':
$post_data = http_build_query($post_data); //对数组或对象使用http_build_query()以提高兼容性
$option_array[CURLOPT_HTTPHEADER][] = 'Content-length: '.strlen($post_data);
break;
default:
}
}
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); //curl_exec执行成功则返回结果（默认返回true)，失败返回false
curl_setopt($ch, CURLOPT_HEADER, false); //启用时会将头文件的信息作为数据流输出
in_array(CURLOPT_CONNECTTIMEOUT, $option_array) OR curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5); //连接超时时间
in_array(CURLOPT_TIMEOUT, $option_array) OR curl_setopt($ch, CURLOPT_TIMEOUT, 30); //数据接收的最大时间
strtoupper($request_method)=='POST' AND curl_setopt($ch, CURLOPT_POST, true); //POST方式时添加
if(strpos(strtolower($url), 'https://')===0){
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); //https请求时跳过cURL验证对等证书
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false); //https请求时跳过cURL验证域名
}
$post_data AND curl_setopt($ch, CURLOPT_POSTFIELDS, $post_data);
count($option_array) AND curl_setopt_array($ch, $option_array);
$output = curl_exec($ch);
$outinfo = curl_getinfo($ch);
$err_no = curl_errno($ch);
$curl_error = $err_no ? 'curl: '.curl_error($ch)." $err_no" : ''; //失败时返回当前会话最后一次错误的字符串
curl_close($ch);
if($return_with_curlinfo){
if($err_no){
log_message('error', $err_no.', '.$curl_error);
return array('data'=>FALSE, 'info'=>$outinfo);
}else{
return array('data'=>$output, 'info'=>$outinfo);
}
}else{
if($err_no){
log_message('error', $err_no.', '.$curl_error);
return FALSE;
}else{
return $output;
}
}
}
