网上有不少相关的信息,有部分文章已经相当不错了,但是还是并不能正确的写入记录,或者说只能兼容比较老的版本(具体是之前哪个版本,还并不知晓)(本文描述的内容,是可以适用于EmpireCMS7.2的),比如有一种说法,只需要修改:phome_ecms_news(一次insert,一次update),phome_ecms_news_data_1(一次insert)两张表,现实是如果只操作这两张表,一般都会出现如下的错误:
而且在删除数据的时候,一样会报一些数据库方面的错误。这种情况下唯一的办法就是查看清楚,每一条新录入的数据,在录入过程中,到底对数据库操作了什么?
打开SQL语句日志功能,可以查看在执行添加一条新信息的时候,数据库到底做了哪些操作,
SET GLOBAL log_output = 'TABLE';SET GLOBAL general_log = 'ON';(记得之后要关闭,不然会产生太多不必要的日志文件)
执行完成后,发现新增记录过程中,所有执行过的SQL语句如下:
mysql> SELECT * from mysql.general_log ORDER BY event_time DESC;
+----------------------------+------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -10-11 13:45:14.338256 | root[root] @ localhost [::1] | 1344 | 1 | Query | SET GLOBAL general_log = 'OFF' |
| -10-11 13:45:09.951519 | root[root] @ localhost [::1] | 1653 | 1 | Quit | |
| -10-11 13:45:09.949531 | root[root] @ localhost [::1] | 1653 | 1 | Query | select andauth from phome_enewsuserloginck where userid='1' |
| -10-11 13:45:09.948513 | root[root] @ localhost [::1] | 1653 | 1 | Query | select userid,groupid,classid,userprikey,uprnd from phome_enewsuser where userid='1' and username='admin' and rnd='og7Q9XlQgP4v7l4Aq1mQ' and checked=0 limit 1 |
| -10-11 13:45:09.947509 | root[root] @ localhost [::1] | 1653 | 1 | Init DB | empirecms |
| -10-11 13:45:09.946506 | root[root] @ localhost [::1] | 1653 | 1 | Query | SET character_set_connection=utf8,character_set_results=utf8,character_set_client=binary,sql_mode='' |
| -10-11 13:45:09.945504 | [root] @ localhost [::1] | 1653 | 1 | Connect | root@localhost on using TCP/IP |
| -10-11 13:45:09.888351 | root[root] @ localhost [::1] | 1652 | 1 | Quit | |
| -10-11 13:45:09.886346 | root[root] @ localhost [::1] | 1652 | 1 | Query | select sitename from phome_enewsbefrom |
| -10-11 13:45:09.885342 | root[root] @ localhost [::1] | 1652 | 1 | Query | select writer from phome_enewswriter |
| -10-11 13:45:09.884339 | root[root] @ localhost [::1] | 1652 | 1 | Query | select firsttitlename,isgoodname from phome_enewspublic limit 1 |
| -10-11 13:45:09.883337 | root[root] @ localhost [::1] | 1652 | 1 | Query | select typeid,tname from phome_enewsinfotype where mid='1' order by myorder |
| -10-11 13:45:09.883337 | root[root] @ localhost [::1] | 1652 | 1 | Query | select tempid,tempname from phome_enewsnewstemp order by modid,tempid |
| -10-11 13:45:09.883337 | root[root] @ localhost [::1] | 1652 | 1 | Query | select tempid,tempname from phome_enewsvotetemp order by tempid |
| -10-11 13:45:09.882334 | root[root] @ localhost [::1] | 1652 | 1 | Query | select definfovoteid from phome_enewsclass where classid='2' |
| -10-11 13:45:09.882334 | root[root] @ localhost [::1] | 1652 | 1 | Query | select ttids from phome_enewsclassadd where classid='2' |
| -10-11 13:45:09.880329 | root[root] @ localhost [::1] | 1652 | 1 | Query | select groupid,adminclass from phome_enewsuser where userid='1' limit 1 |
| -10-11 13:45:09.880329 | root[root] @ localhost [::1] | 1652 | 1 | Query | select doall,doselfinfo,doaddinfo,doeditinfo,dodelinfo,docheckinfo,dogoodinfo,dodocinfo,domoveinfo,domustcheck,docheckedit from phome_enewsgroup where groupid='1' |
| -10-11 13:45:09.880329 | root[root] @ localhost [::1] | 1652 | 1 | Query | select groupid,groupname from phome_enewsmembergroup order by level |
| -10-11 13:45:09.879326 | root[root] @ localhost [::1] | 1652 | 1 | Query | select userid,groupid,classid,userprikey,uprnd from phome_enewsuser where userid='1' and username='admin' and rnd='og7Q9XlQgP4v7l4Aq1mQ' and checked=0 limit 1 |
| -10-11 13:45:09.879326 | root[root] @ localhost [::1] | 1652 | 1 | Query | select andauth from phome_enewsuserloginck where userid='1' |
| -10-11 13:45:09.878323 | root[root] @ localhost [::1] | 1652 | 1 | Query | SET character_set_connection=utf8,character_set_results=utf8,character_set_client=binary,sql_mode='' |
| -10-11 13:45:09.878323 | root[root] @ localhost [::1] | 1652 | 1 | Init DB | empirecms |
| -10-11 13:45:09.877320 | [root] @ localhost [::1] | 1652 | 1 | Connect | root@localhost on using TCP/IP |
| -10-11 13:45:07.798427 | root[root] @ localhost [::1] | 1651 | 1 | Quit | |
| -10-11 13:45:07.796423 | root[root] @ localhost [::1] | 1651 | 1 | Query | insert into phome_enewsdolog(username,logip,logtime,enews,doing,pubid,ipport) values('admin','::1','-10-11 13:45:07','AddNews','classid=2<br>id=2<br>title=title','1000010000000002','63863') |
| -10-11 13:45:07.794417 | root[root] @ localhost [::1] | 1651 | 1 | Query | select id,classid,ttid,onclick,plnum,totaldown,newspath,filename,userid,username,firsttitle,isgood,ispic,istop,isqf,ismember,isurl,truetime,lastdotime,havehtml,groupid,userfen,titlefont,titleurl,stb,fstb,restb,keyboard,title,ftitle,newstime,titlepic,smalltext,diggtop from phome_ecms_news where isgood>0 and ispic=1 order by newstime desc limit 4 |
| -10-11 13:45:07.793414 | root[root] @ localhost [::1] | 1651 | 1 | Query | select tempid,modid,temptext,showdate,listvar,subnews,rownum,docode from phome_enewsbqtemp where tempid='12' |
| -10-11 13:45:07.793414 | root[root] @ localhost [::1] | 1651 | 1 | Query | select classid,classname,islast,sonclass,tbname,intro,classimg,infos from phome_enewsclass where bclassid='0' and showclass=0 order by myorder,classid |
| -10-11 13:45:07.790407 | root[root] @ localhost [::1] | 1651 | 1 | Query | select bq,funname from phome_enewsbq where isclose=0 order by bqid |
| -10-11 13:45:07.789403 | root[root] @ localhost [::1] | 1651 | 1 | Query | select temptext,showdate from phome_enewsnewstemp where tempid='1' limit 1 |
| -10-11 13:45:07.789403 | root[root] @ localhost [::1] | 1651 | 1 | Query | select myvar,varvalue from phome_enewstempvar where isclose=0 order by myorder desc,varid |
| -10-11 13:45:07.788401 | root[root] @ localhost [::1] | 1651 | 1 | Query | select ttid,onclick,plnum,totaldown,newspath,filename,userid,username,firsttitle,isgood,ispic,istop,isqf,ismember,isurl,truetime,lastdotime,havehtml,groupid,userfen,titlefont,titleurl,stb,fstb,restb,keyboard,title,titlepic,newstime,ftitle,smalltext,diggtop from phome_ecms_news where id='2' limit 1 |
| -10-11 13:45:07.788401 | root[root] @ localhost [::1] | 1651 | 1 | Query | select keyid,dokey,newstempid,closepl,infotags,writer,befrom,newstext from phome_ecms_news_data_1 where id='2' limit 1 |
| -10-11 13:45:07.787398 | root[root] @ localhost [::1] | 1651 | 1 | Query | update phome_enewspublic_update set lastnuminfo=lastnuminfo+1,lastnuminfotb='|1,38|',todaynuminfo=todaynuminfo+1 limit 1 |
| -10-11 13:45:07.787398 | root[root] @ localhost [::1] | 1651 | 1 | Query | update phome_enewsfile_1 set pubid='1000010000000002',classid='2',id='2',cjid=0 where cjid='1539236638' |
| -10-11 13:45:07.787398 | root[root] @ localhost [::1] | 1651 | 1 | Query | update phome_ecms_news set filename='2',titleurl='/youhua/seo/-10-11/2.html' where id='2' |
| -10-11 13:45:07.787398 | root[root] @ localhost [::1] | 1651 | 1 | Query | select count(*) as total from phome_enewsinfovote where pubid='1000010000000002' limit 1 |
| -10-11 13:45:07.786395 | root[root] @ localhost [::1] | 1651 | 1 | Query | update phome_enewsclass set allinfos=allinfos+1,infos=infos+1 where classid='2' limit 1 |
| -10-11 13:45:07.786395 | root[root] @ localhost [::1] | 1651 | 1 | Query | select lasttimeinfo,lastnuminfotb,todaytimeinfo,todaytimepl,todaynuminfo,todaynumpl from phome_enewspublic_update limit 1 |
| -10-11 13:45:07.785392 | root[root] @ localhost [::1] | 1651 | 1 | Query | insert into phome_ecms_news(id,classid,ttid,onclick,plnum,totaldown,newspath,filename,userid,username,firsttitle,isgood,ispic,istop,isqf,ismember,isurl,truetime,lastdotime,havehtml,groupid,userfen,titlefont,titleurl,stb,fstb,restb,keyboard,title,ftitle,newstime,titlepic,smalltext) values('2','2','0','0',0,'0','-10-11','','1','admin','0','0','0','0','0',0,'0','1539236707','1539236707','1','0','0','','','1','1','1','keyword','title','fubiao','1539236638','','content desc') |
| -10-11 13:45:07.785392 | root[root] @ localhost [::1] | 1651 | 1 | Query | insert into phome_ecms_news_data_1(id,classid,keyid,dokey,newstempid,closepl,haveaddfen,infotags,writer,befrom,newstext) values('2','2','','1','0','0',0,'','','','<p> content content content content content content content content content content content content</p>') |
| -10-11 13:45:07.784391 | root[root] @ localhost [::1] | 1651 | 1 | Query | insert into phome_ecms_news_index(classid,checked,newstime,truetime,lastdotime,havehtml) values('2','1','1539236638','1539236707','1539236707','1') |
| -10-11 13:45:07.783387 | root[root] @ localhost [::1] | 1651 | 1 | Query | select sonclass from phome_enewsmod where mid='1' |
| -10-11 13:45:07.783387 | root[root] @ localhost [::1] | 1651 | 1 | Query | select id from phome_ecms_news where (title like '%keyword%') and (classid in (2)) and id<>0 order by newstime desc limit 10 |
| -10-11 13:45:07.781381 | root[root] @ localhost [::1] | 1651 | 1 | Query | select groupid,adminclass from phome_enewsuser where userid='1' limit 1 |
| -10-11 13:45:07.781381 | root[root] @ localhost [::1] | 1651 | 1 | Query | select doall,doselfinfo,doaddinfo,doeditinfo,dodelinfo,docheckinfo,dogoodinfo,dodocinfo,domoveinfo,domustcheck,docheckedit from phome_enewsgroup where groupid='1' |
| -10-11 13:45:07.781381 | root[root] @ localhost [::1] | 1651 | 1 | Query | select classid,modid,listdt,haddlist,sametitle,addreinfo,wburl,repreinfo from phome_enewsclass where classid='2' and islast=1 limit 1 |
| -10-11 13:45:07.773361 | root[root] @ localhost [::1] | 1651 | 1 | Query | select userid,groupid,classid,userprikey,uprnd from phome_enewsuser where userid='1' and username='admin' and rnd='og7Q9XlQgP4v7l4Aq1mQ' and checked=0 limit 1 |
| -10-11 13:45:07.773361 | root[root] @ localhost [::1] | 1651 | 1 | Query | select andauth from phome_enewsuserloginck where userid='1' |
| -10-11 13:45:07.772358 | root[root] @ localhost [::1] | 1651 | 1 | Query | SET character_set_connection=utf8,character_set_results=utf8,character_set_client=binary,sql_mode='' |
| -10-11 13:45:07.772358 | root[root] @ localhost [::1] | 1651 | 1 | Init DB | empirecms |
| -10-11 13:45:07.768348 | [root] @ localhost [::1] | 1651 | 1 | Connect | root@localhost on using TCP/IP
我们剔除掉一些SELECT语句和无用的系统相关的SET语句,只留下update和insert语句,最后经过排查,剩下这些操作还是有用的,然后在使用下面语句生成的记录上进行删除,修改操作,都是正常的,因此可以断定,只需要对如下表进行操作,
就可以完成新增记录的操作。
1.insert into phome_ecms_news_index(classid,checked,newstime,truetime,lastdotime,havehtml) values('2','1','1539238638','1539238707','1539238707','1');
2.insert into phome_ecms_news_data_1(id,classid,keyid,dokey,newstempid,closepl,haveaddfen,infotags,writer,befrom,newstext) values('4','2','','1','0','0',0,'','','','<p> content content content content content content content content content content content content</p>')
3.insert into phome_ecms_news(id,classid,ttid,onclick,plnum,totaldown,newspath,filename,userid,username,firsttitle,isgood,ispic,istop,isqf,ismember,isurl,truetime,lastdotime,havehtml,groupid,userfen,titlefont,titleurl,stb,fstb,restb,keyboard,title,ftitle,newstime,titlepic,smalltext) values('4','2','0','0',0,'0','-10-11','','1','admin','0','0','0','0','0',0,'0','1539238707','1539238707','1','0','0','','','1','1','1','keyword','title','fubiao','1539238638','','content desc')
4.update phome_enewsclass set allinfos=allinfos+1,infos=infos+1 where classid='2' limit 1
5.update phome_ecms_news set filename='4',titleurl='/youhua/seo/-10-11/4.html' where id='4'
6.update phome_enewsfile_1 set pubid='1000010000000002',classid='2',id='2',cjid=0 where cjid='1539236638'
7.update phome_enewspublic_update set lastnuminfo=lastnuminfo+1,lastnuminfotb='|1,40|',todaynuminfo=todaynuminfo+1 limit 1
8.insert into phome_enewsdolog(username,logip,logtime,enews,doing,pubid,ipport) values('admin','::1','-10-11 13:45:07','AddNews','classid=2<br>id=2<br>title=title','1000010000000002','63863')
使用过程中发现:update phome_enewsfile_1 set pubid='1000010000000002',classid='2',id='2',cjid=0 where cjid='1539236638'
这个其实也是一条无效操作,因为这个表始终是空的,后续会不会在某些时候有用,暂时还不得而知,先去掉这条语句,后来经过研究发现这条记录是用于存储附件的,
因为暂时没有附件,所以可以先不处理这条记录。
最后一条是日志记录,经过测试发现日志是可以不写的,不会影响操作的正常进行,所以可以先忽略。
这样最后有用的剩下六条:
1.insert into phome_ecms_news_index(classid,checked,newstime,truetime,lastdotime,havehtml) values('2','1','1539238638','1539238707','1539238707','1');
2.insert into phome_ecms_news_data_1(id,classid,keyid,dokey,newstempid,closepl,haveaddfen,infotags,writer,befrom,newstext) values('4','2','','1','0','0',0,'','','','<p> content content content content content content content content content content content content</p>')
3.insert into phome_ecms_news(id,classid,ttid,onclick,plnum,totaldown,newspath,filename,userid,username,firsttitle,isgood,ispic,istop,isqf,ismember,isurl,truetime,lastdotime,havehtml,groupid,userfen,titlefont,titleurl,stb,fstb,restb,keyboard,title,ftitle,newstime,titlepic,smalltext) values('4','2','0','0',0,'0','-10-11','','1','admin','0','0','0','0','0',0,'0','1539238707','1539238707','1','0','0','','','1','1','1','keyword','title','fubiao','1539238638','','content desc')
4.update phome_enewsclass set allinfos=allinfos+1,infos=infos+1 where classid='2' limit 1
5.update phome_ecms_news set filename='4',titleurl='/youhua/seo/-10-11/4.html' where id='4'
6.update phome_enewspublic_update set lastnuminfo=lastnuminfo+1,lastnuminfotb='|1,40|',todaynuminfo=todaynuminfo+1 limit 1
我们只需要在导入数据库时做好这些操作就可以了。
insert into phome_ecms_news_index(classid,checked,newstime,truetime,lastdotime,havehtml) values('2','1','1539238638','1539238707','1539238707','1');
insert into phome_ecms_news_data_1(id,classid,keyid,dokey,newstempid,closepl,haveaddfen,infotags,writer,befrom,newstext) values('4','2','','1','0','0',0,'','','','<p> content content content content content content content content content content content content</p>')
insert into phome_ecms_news(id,classid,ttid,onclick,plnum,totaldown,newspath,filename,userid,username,firsttitle,isgood,ispic,istop,isqf,ismember,isurl,truetime,lastdotime,havehtml,groupid,userfen,titlefont,titleurl,stb,fstb,restb,keyboard,title,ftitle,newstime,titlepic,smalltext) values('4','2','0','0',0,'0','-10-11','','1','admin','0','0','0','0','0',0,'0','1539238707','1539238707','1','0','0','','','1','1','1','keyword','title','fubiao','1539238638','','content desc')
update phome_enewsclass set allinfos=allinfos+1,infos=infos+1 where classid='2' limit 1
update phome_ecms_news set filename='@@IDENTITY',titleurl=concat('/youhua/seo/-10-11/',@@IDENTITY,'.html') where id=@@IDENTITY
update phome_enewspublic_update set lastnuminfo=lastnuminfo+1,lastnuminfotb='|1,40|',todaynuminfo=todaynuminfo+1 limit 1
为了让文章ID唯一,我们需要使用@@IDENTITY标识符,来实现唯一标识的传递,那么就不能直接用上面的顺序来进行数据库填写,因为@@IDENTITY(@@identity是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值,是系统定义的全局变量。)
insert into phome_ecms_news_index(classid,checked,newstime,truetime,lastdotime,havehtml) values('2','1','1539238888','1539238888','1539238888','1');
insert into phome_ecms_news(id,classid,ttid,onclick,plnum,totaldown,newspath,filename,userid,username,firsttitle,isgood,ispic,istop,isqf,ismember,isurl,truetime,lastdotime,havehtml,groupid,userfen,titlefont,titleurl,stb,fstb,restb,keyboard,title,ftitle,newstime,titlepic,smalltext) values('6','2','0','0',0,'0','-10-11','','1','admin','0','0','0','0','0',0,'0','1539238888','1539238888','1','0','0','','','1','1','1','keyword','title','fubiao','1539238888','','content desc')
update phome_ecms_news set filename='6',titleurl=concat('/youhua/seo/-10-11/6.html') where id=6
insert into phome_ecms_news_data_1(id,classid,keyid,dokey,newstempid,closepl,haveaddfen,infotags,writer,befrom,newstext) values('6','2','','1','0','0',0,'','','','<p> content content content content content content content content content content content content</p>')
update phome_enewsclass set allinfos=allinfos+1,infos=infos+1 where classid='2' limit 1;
update phome_enewspublic_update set lastnuminfo=lastnuminfo+1,lastnuminfotb='|1,43|',todaynuminfo=todaynuminfo+1 limit 1;
把部分内容替换成系统标识以后,如下所示:
insert into phome_ecms_news_index(classid,checked,newstime,truetime,lastdotime,havehtml) values('2','1','[系统时间戳]','[系统时间戳]','[系统时间戳]','1')
insert into phome_ecms_news(id,classid,ttid,onclick,plnum,totaldown,newspath,filename,userid,username,firsttitle,isgood,ispic,istop,isqf,ismember,isurl,truetime,lastdotime,havehtml,groupid,userfen,titlefont,titleurl,stb,fstb,restb,keyboard,title,ftitle,newstime,titlepic,smalltext) values(@@IDENTITY,'2','0','0',0,'0','-10-11','','1','admin','0','0','0','0','0',0,'0','[系统时间戳]','[系统时间戳]','1','0','0','','','1','1','1','keyword','火车头采集器使用‘导入数据库’形式发布数据到帝国CMS数据库的过程(原创)适用于ECMS7.2','','[系统时间戳]','','');
update phome_ecms_news set filename='@@IDENTITY',titleurl=concat('/youhua/seo/-10-11/',@@IDENTITY,'.html') where id=@@IDENTITY;
insert into phome_ecms_news_data_1(id,classid,keyid,dokey,newstempid,closepl,haveaddfen,infotags,writer,befrom,newstext) values(@@IDENTITY,'2','','1','0','0',0,'','','','[标签:内容]');
update phome_enewsclass set allinfos=allinfos+1,infos=infos+1 where classid='2' limit 1;
update phome_enewspublic_update set lastnuminfo=lastnuminfo+1,lastnuminfotb=concat('|1,',lastnuminfo,'|'),todaynuminfo=todaynuminfo+1 limit 1;
具体操作如下图:
通过测试发布,查看发布后的情况,可以看到已经发布的记录:
更新记录,生成html文件后,打开如下图所示:
这样火车头的内容发布以数据库形式发布,到这里就结束了。上面这五条sql语句,是数据库发布必备的,整个过程断断续续调试了几天,发现火车头采集器跟帝国的文章发布相当繁琐,
而且帝国CMS的发布好像用的人不是很多,写出来共享给大家说不定有用,过程相当不易,觉得有用的朋友们点个赞。。。
火车头采集器使用‘导入数据库’形式发布数据到帝国CMS数据库的过程(原创)适用于ECMS7.2