Skip to content


根据mysql id产生分级目录并导出

项目中的静态页根据信息表的id来自动分级目录,结构为每3位为一级。
如id=347234 就会先补全至9位->000347234再分级成/000/347/show_234.html。

程序中是用php来做的,目前需要导出一批地址数据,懒的写程序就直接用sql了。
SELECT id,CompanyName,CONCAT(‘http://www.c1gstudio.com/’,left(lpad(id,9,0),3),’/’,SUBSTR(lpad(id,9,0),4,3),’/show_’,right(lpad(id,9,0),3),’.html’) as url
FROM `t_reprint`
WHERE FromSite=’C1G军火库’ and `OperatorID`=’1′

在phpmyadmin 3.0.0中导出界面可能对中文支持不好,导致语句中断,不能导出
在远程创建用户,在本地phpmyadmin 2.8.2.4用远程用户导出,报substr函数未找到…(但在本地测试是可以的)

使用命令行导出
./mysql -hlocalhost -uroot -p
输入密码
use db;
SELECT id,CompanyName,CONCAT(‘http://www.c1gstudio.com/’,left(lpad(id,9,0),3),’/’,SUBSTR(lpad(id,9,0),4,3),’/show_’,right(lpad(id,9,0),3),’.html’) as url
INTO OUTFILE ‘./output.txt’
FROM `t_reprint`
WHERE FromSite=’C1G军火库’ and `OperatorID`=’1′ ;
 然后在mysql/var/下就可以找到output.txt

Posted in Mysql, 技术.

Tagged with , .


No Responses (yet)

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.



Some HTML is OK

or, reply to this post via trackback.