mysql sql

 grant select on crawlerfeedsky.* to spider@'218.249.153.228' identified by 'crawler';
flush privileges;

 

mysqldump --opt -uspider -pcrawler crawlerfeedsky > /home/mahaibo/dump-20070921 &

 mysql -uroot -pSina@Net crawlerfeedsky < dump-20070921 &

 show create table feed;查看创建表的sql

select * from table into outfile 'filename';

select id from feed into outfile 'id.txt'

load data infile 'filename' into table xxx;

索引：

alter table 表名 add key(字段1, 字段2, 字段3);

alter table 表名 add unique key(字段1, 字段2, 字段3);

ALTER TABLE `rss` ADD `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

./mysqladmin -uspider -p shutdown

./mysqld_safe &

1. 添加字符串

 

http://www.wretch.cc/blog/${burl}&rss20=1 更改为:
http://www.wretch.cc/blog/${burl}&rss20=1&fetch=1

 

update 表 set 字段 = concat(字段, '&fetch=1') where 字段 like 'http://www.wretch.cc/blog/%&rss20=1';

如:update feed set raw_url = concat(raw_url, '&fetch=1') where raw_url like 'http://www.wretch.cc/blog/%&rss20=1';

 

 

myisamchk -r score.MYI 修复索引结构

 

索引：

2个字段的索引写在一个索引文件：UNIQUE KEY `article_id_index` (`feed_id`,`article_id`)

或者KEY `article_id_index` (`feed_id`,`article_id`)

联合id:

PRIMARY KEY (`itemId`,`itemTitle`),
KEY `ttt` (`itemId`,`itemTitle`)

 

 

mysql取7天前的数据：

select * from result where updateTime < (now() - interval 7 day)

DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
  `id` int(11) NOT NULL auto_increment,
  `srcId` bigint NOT NULL default '0',
  `rawUrl` varchar(100) NOT NULL,
  `rawUrlMd5` varchar(100) NOT NULL,
  `updateTime` varchar(100) NOT NULL,
  `resultCode` int(10) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index_two` (`rawUrlMd5`,`updateTime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

创建索引的另一种方式：

DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
  `id` int(11) NOT NULL auto_increment,
  `srcId` bigint NOT NULL default '0',
  `rawUrl` varchar(255) NOT NULL,
  `rawUrlMd5` varchar(255) NOT NULL,
  `updateTime` varchar(255) NOT NULL,
  `resultCode` int(10) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE INDEX index_rawUrlMd5 ON result(rawUrlMd5);

 

修改密码:

mysql>set   password   for   root@localhost   =   password('');
mysql>FLUSH   PRIVILEGES;