gistfile1.txt

mysql
=====
Created 星期三 30 三月 2016



##### 数据库


db_237 = MySQLUtility('112.11.119.237', 'ebu_data', 'slave', 'admin@edb')
db_ssc = MySQLUtility('116.228.3.111', 'haoyunmama', 'root', 'admin@edb')



#### 索引

##### 增加索引
ALTER TABLE  `pre_weixin_post` ADD UNIQUE (`p_sn`);

##### 删除索引
ALTER TABLE good_booked DROP INDEX good_id;


##### =======================




#### 字段

##### 更改字段
ALTER TABLE  `pre_weixin_post` CHANGE  `p_author`  `p_author` VARCHAR( 20 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ;

ALTER TABLE  `task_url` CHANGE  `from_task_hash`   `from_task_hash` BIGINT( 20 ) UNSIGNED NOT NULL ;

##### 添加字段
ALTER TABLE  `cookie` ADD  `JSESSIONID` VARCHAR( 25 ) NOT NULL AFTER  `IPLOC` ;

ALTER TABLE  `wx_gzh` ADD  `is_active` BOOLEAN NOT NULL DEFAULT TRUE AFTER  `gzh_icon` ;

#### 删除字段
ALTER TABLE  `wx_gzh` DROP  `gzh_openid` ;


##### =======================


### 命令
sudo ifup -a
sudo ifdown -a

##### 双层排序
select id, level, update_at  from (select * from task_pool where level=(select max(level) from task_pool)) as tmpview order by update_at limit 1\G

##### ============
select distinct a.groupid, b.name  from task_pool as a join task_group as b on a.groupid=b.id;


##### =============
DELETE FROM crawl_news_html USING crawl_news_html, news WHERE crawl_news_html.url_hash=news.url_hash and news.id>135868;


##### =插入
INSERT into  crawl_words(id, word, sdate, edate) select id, keywords,sdate, edate from event_def where id>223;


##### ======
select * from danmu where url_hash in(select group_concat(url_hash) from video where kid=155) and (pubtime>=1475251200);


##### ======导出
	1、导出數據库為dbname的表结构（其中用戶名為root,密码為dbpasswd,生成的脚本名為db.sql）
	mysqldump -uroot -pdbpasswd -d dbname >db.sql;

	2、导出數據库為dbname某张表(test)结构
	mysqldump -uroot -pdbpasswd -d dbname test>db.sql;

	3、导出數據库為dbname所有表结构及表數據（不加-d）
	mysqldump -uroot -pdbpasswd  dbname >db.sql;

	4、导出數據库為dbname某张表(test)结构及表數據（不加-d）
	mysqldump -uroot -pdbpasswd dbname test>db.sql;