标签 sql 下的文章

在实际工作中，通常会遇到某个进程或者请求运行的十分缓慢，其中大部分的时间都花在了数据库的查询和写入上，每次遇到这个问题就十分头痛。首先，鉴于我们是很底层的程序员，没法花钱给企业加硬件，那我们能完成的事情就是用头脑取分析并优化每一条查询，来获得查询效率的提升。

老高总结了一下工作中遇到的问题，以后再遇到相同的问题后可以快速排查。

阅读剩余部分

合并多个sql文件

win:

copy *.sql merged.sql
# \b 指二进制合并，可用于图片等二进制文件
# \a 指ASCII合并
copy \b *.sql merged.sql

# 小技巧，可实现文本隐藏
copy 1.jpg/b + 2.txt/a 3.jpg

linux:

# 合并所有扩展名为.sql的文件
cat *.sql>>merged.sql

主键

-- 为当前表添加主键
ALTER TABLE `tablename`
	ADD COLUMN id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
	ADD PRIMARY KEY (id);

-- 删除主键

ALTER TABLE `tablename`
	DROP PRIMARY KEY;

创建数据库

# utf8mb4_unicode_ci更准
CREATE DATABASE IF NOT EXISTS typecho DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
# utf8mb4_general_ci更快
CREATE DATABASE IF NOT EXISTS typecho DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE typecho DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

创建用户并提供相应权限

# 只是创建用户
CREATE USER phpergao@'localhost' IDENTIFIED BY 'yourpasswd';

# 赋予权限
GRANT select,update on phpergao.* to phpergao@'localhost';

GRANT index ON phpergao.* TO phpergao@'192.168.0.%';

# 创建用户并赋予权限
GRANT ALL PRIVILEGES ON phpergao.* TO 'phpergao'@'localhost' IDENTIFIED BY 'yourpasswd';

# 相反的revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：
REVOKE ALL PRIVILEGES ON phpergao.* FROM 'phpergao'@'localhost';

# ALL PRIVILEGES 可以换为select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。

# 删除用户
DELETE FROM user WHERE User='phpergao' and Host='localhost';

# 修改用户密码
UPDATE USER SET PASSWORD = PASSWORD ('newpasswd') WHERE	USER = 'phpergao' AND HOST = 'localhost';

刷新权限

FLUSH PRIVILEGES;

查看用户权限

# 查看自己的权限
SHOW GRANTS;
# 查看其他人的权限
SHOW GRANTS FOR 'phpergao'@'%';

新建数据表

DROP TABLE IF EXISTS `workers_info`;  
CREATE TABLE `workers_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `workername` varchar(20) NOT NULL,
  `sex` enum('F','M','S') DEFAULT 'S',
  `salary` int(11) DEFAULT '0',
  `email` varchar(30) DEFAULT NULL,
  `EmployedDates` date DEFAULT NULL,
  `department` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

忘记了mysql密码

修改配置文件

[mysqld] 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock 
# ADD
skip-name-resolve 
skip-grant-tables

然后重启MySQL服务并免密码登录

service mysqld restart
mysql

执行修改密码SQL

将root用户的密码统一设为'admin'
UPDATE mysql.user SET Password=password('123456') WHERE User='root';

还原MySQL配置文件并重启服务

修改用户登录HOST

UPDATE mysql.user SET Host='&' WHERE User='root';

参考：

http://renxiangzyq.iteye.com/blog/763837

博客的文章渐渐多了起来，之前随意的分类就需要好好想整理一下。

为了图方便，直接在数据库里做了替换查询，不料typecho和WP一样，都会在metas表里存着分类和tag的统计信息，统计了这个分类下有多少篇文章，这个统计信息显示在分类和TAG管理页面。

也就是说，如果正常再文章编辑里修改分类并保存，统计信息会走一加一减这个过程，而暴力数据库修改不会触发。

而这种混乱导致老高的瞬间变身不搞不舒服斯基，下面我们一步一步来修复这个统计信息。

首先，找到所有分类（标签同理）

SELECT mid FROM typecho_metas AS m WHERE m.type = 'category';

然后，找到分类关系表，统计文章数

SELECT r.mid,
       count(cid)
FROM typecho_relationships as r
WHERE r.mid IN
    (SELECT mid
     FROM typecho_metas AS m
     WHERE m.type='category')
GROUP BY r.mid;

再次，我们提取出分类名称

SELECT r.mid,
       m.name,
       count(cid)
FROM typecho_relationships as r
LEFT JOIN typecho_metas as m ON m.mid=r.mid
WHERE r.mid IN
    (SELECT mid
     FROM typecho_metas AS m
     WHERE m.type='category')
GROUP BY r.mid;

最后，以上一个查询为基础修改原始表metas

UPDATE typecho_metas,
  (SELECT r.mid AS mid,
          count(cid) AS COUNT
   FROM typecho_relationships AS r
   WHERE r.mid IN
       (SELECT mid
        FROM typecho_metas AS m
        WHERE m.type='category')
   GROUP BY r.mid) AS tmp
SET typecho_metas.COUNT = tmp.COUNT
WHERE tmp.mid = typecho_metas.mid;

上面的SQL也表明了如果使用两张表跟新数据。

突然发现使用分类管理的合并到功能也可以，但是没我这个快 B)

织梦cms不知大家用过没有，功能比较强大，但是对于我还是有一些不够，所以我总结了一些经常会使用的SQL，比如批量修改文章发布人、替换栏目字符串、批量修改文章点击数、批量修改文章命名规则等，这些SQL使用dedecmsv5.7测试，其他版本不保证。

老高温馨提醒：使用此SQL务必备份数据库

清空所有文章

TRUNCATE TABLE dede_addonarticle;
TRUNCATE TABLE dede_archives;
TRUNCATE TABLE dede_arctiny;

批量修改文章发布人

UPDATE dede_arctiny AS a
SET a.mid=2;
UPDATE dede_archives AS a
SET a.mid=2;
/*mid为管理员ID*/

替换栏目字符串

UPDATE dede_arctype as a
SET a.typename=REPLACE(a.typename,'AAA','BBB')
#AAA替换为BBB

修改文章点击数

/*随机数为300-1300*/
UPDATE dede_archives AS a
SET a.click=FLOOR(300 + (RAND() * 1001));

批量修改文章命名规则

UPDATE
dede_arctype AS a
SET
a.namerule='{typedir}/{aid}.html'

查看栏目表

SELECT dede_arctype.typename,
       dede_arctype.description,
       dede_arctype.keywords,
       dede_arctype.seotitle,
       dede_arctype.namerule,
       dede_arctype.namerule2
FROM dede_arctype
ORDER BY typename;

所有文章都改为动态

#ismake的值为1时为静态，-1 时表示动态
update dede_archives set ismake=-1;
#将所有栏目设置为“使用静态页”，
update dede_arctype set isdefault=1

运行SQL一定要备份，以防万一！

先来看看每个表的功能吧

wp_commentmeta：存储评论的元数据 wp_comments：存储评论 wp_links：存储友情链接（Blogroll） wp_options：存储WordPress系统选项和插件、主题配置 wp_postmeta：存储文章（包括页面、上传文件、修订）的元数据 wp_posts：存储文章（包括页面、上传文件、修订） wp_terms：存储每个目录、标签 wp_term_relationships：存储每个文章、链接和对应分类的关系 wp_term_taxonomy：存储每个目录、标签所对应的分类 wp_usermeta：存储用户的元数据 wp_users：存储用户信息

删除残留垃圾数据

使用WordPress经常换主题删主题，装插件删插件很正常，但是简单的删除并不彻底，数据库会有残留，多余的数据保留在post_meta表格里，久而久之就成了一堆可观的垃圾。可使用下面的SQL语句来清除不需要的postmeta值。有益于加快数据库运行速度，减小数据。 执行SQL语句

DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock';
DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';

删除草稿修订版本

在WordPress后台中编辑文章时，系统会自动保存许多修订的副本。过多的修订记录会加重数据库的负担并造成了资源的浪费。数据库越来越庞大，增加了数据检索影响页面的加载时间。 执行SQL语句

DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

注意：此方法将删除所有的文章的所有修订版，包括相关的meta数据。

处理未使用的标签

在WordPress数据库中，如果你使用一个查询语句手动来删除旧的文章，旧的标签却仍然会保留并在你的标签云/列表中出现。你可以使用下面的方法识别未使用的标签并将它删除。 执行SQL语句

SELECT * FROM wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id
INNER JOIN wp_term_relationships wtr ON wtr.term_taxonomy_id=wtt.term_taxonomy_id
LEFT JOIN wp_posts wp ON wp.ID=wtr.object_id
WHERE taxonomy='post_tag'
AND ID IS null
AND NOT EXISTS(SELECT * From wp_terms wt2
INNER JOIN wp_term_taxonomy wtt2 ON wt2.term_id=wtt2.term_id WHERE wtt2.parent=wt.term_id) ORDER BY name;

修复和优化

执行完所有的语句之后，全选所有的表，然后选择“修复表”和“优化表”。 (来源于互联网)