MySQL复制、修改表基本知识
CREATE TABLE tab2 AS (SELECT * FROM tab1)
这种做法表的存储引擎会采用服务器默认的存储引擎而不是源表的存储引擎，此种复制方法把表的内容也一起复制过来了。
CREATE TALBE tab2 ENGINE=MYISAM, CHARSET='UTF8' AS (SELECT * FROM tab1)
可以自己指定存储引擎和字符集，弥补方法一的不足
CREATE TABLE tab2 LIKE tab1
使用和tab1表相同的结构来创建一个新表，列名、数据类型、空指和索引也将复制，但是表的内容不会被复制。外键和专用的权限也没有被复制。
MySQL复制表结构及数据到新表
CREATE TABLE tab_new SELECT * FROM tab_old
复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO tab1 SELECT * FROM tab2
复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO tab1(字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM tab2
更改表名
ALTER TABLE employee RENAME TO staff
更改表注释
ALTER TABLE 表名 COMMENT='新注释';
更改列类型
ALTER TABLE employee MODIFY COLUMN truename VARCHAR(10) NOT NULL DEFAULT ''
修改自增字段下一条记录auto_increment的值为9
ALTER TABLE my_tablename AUTO_INCREMENT=9
修改列注释的时候需要指定{type}
ALTER TABLE student MODIFY COLUMN stu_id INT COMMENT '学号';
参考：http://blog.sina.com.cn/s/blog_72aace390102uwgg.html
更改列名
ALTER TABLE employee CHANGE COLUMN truename employeename VARCHAR(10) NOT NULL DEFAULT ''
添加默认值
ALTER TABLE employee ALTER COLUMN truename SET DEFAULT ''
删除默认值
ALTER TABLE employee ALTER COLUMN truename DEOP DEFAULT
添加列
ALTER TABLE mytable ADD COLUMN addprogram VARCHAR(100) NOT NULL COMMENT '文档添加程序' AFTER title;
删除列
ALTER TABLE mytable DROP COLUMN title
mysql创建索引
1、添加PRIMARY KEY（主键索引）
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2、添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )
3、添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4、添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5、添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
MySQL批量insert
('admin', '张三'),
('test', '测试'),
('edgesun', '李四')
千万级数据库表去重复的方法
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)
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
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);
