我有相当多的老帖子,其中包含一个高度和宽度的img标签,数字周围没有双引号。例如:
<img height=319 alt="" src="http://www.example.com/images/myexample.jpg" width=496>
我不知道如何编写MySQL查询来查找高度和宽度数字,并用双引号将其括起来。虽然MySQL查询是首选,但如果这不可能,那么也许有人可以建议我使用一个正则表达式和一个PHP脚本来解决这个问题。
我有相当多的老帖子,其中包含一个高度和宽度的img标签,数字周围没有双引号。例如:
<img height=319 alt="" src="http://www.example.com/images/myexample.jpg" width=496>
我不知道如何编写MySQL查询来查找高度和宽度数字,并用双引号将其括起来。虽然MySQL查询是首选,但如果这不可能,那么也许有人可以建议我使用一个正则表达式和一个PHP脚本来解决这个问题。
你一定会喜欢这个的
首先,这里是一个加载了数据的示例表:
mysql> use junk
Database changed
mysql> drop table todd;
Query OK, 0 rows affected (0.01 sec)
mysql> create table todd (id int not null auto_increment,url VARCHAR(255),
-> primary key (id)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO todd (url) VALUES
-> (\'<img height=319 alt="" src="http://www.example.com/images/myexample.jpg" width=496>\'),
-> (\'<img height=329 alt="" src="http://www.example.com/images/myexample.jpg" width=130>\'),
-> (\'<img height=339 alt="" src="http://www.example.com/images/myexample.jpg" width=206>\'),
-> (\'<img height=349 alt="" src="http://www.example.com/images/myexample.jpg" width=498>\'),
-> (\'<img height=359 alt="" src="http://www.example.com/images/myexample.jpg" width=499>\');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from todd;
+----+-------------------------------------------------------------------------------------+
| id | url |
+----+-------------------------------------------------------------------------------------+
| 1 | <img height=319 alt="" src="http://www.example.com/images/myexample.jpg" width=496> |
| 2 | <img height=329 alt="" src="http://www.example.com/images/myexample.jpg" width=130> |
| 3 | <img height=339 alt="" src="http://www.example.com/images/myexample.jpg" width=206> |
| 4 | <img height=349 alt="" src="http://www.example.com/images/myexample.jpg" width=498> |
| 5 | <img height=359 alt="" src="http://www.example.com/images/myexample.jpg" width=499> |
+----+-------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql>
必须依次运行两个查询:这个在数字的高度加上双引号
UPDATE
(select id,CONCAT(bftoken,token,\'"\',num,\'"\',substr(aftoken,num_length+1)) newurl
FROM (select id,token,
substr(b.url,1,a.hpos - 1) bftoken,
substr(b.url,a.hpos + length(a.token)) aftoken,
substr(b.url,a.hpos + length(a.token))+0 num,
length(substr(b.url,a.hpos + length(a.token))+0) num_length
from
(select id,token,LOCATE(token,url) hpos
from todd,(select \'height=\' token) w
WHERE LOCATE(CONCAT(token,\'"\'),url)=0) A
INNER JOIN todd B USING (id)) AA) AAA
INNER JOIN todd BBB USING (id)
SET BBB.url = AAA.newurl;
这个在数字宽度周围加上双引号UPDATE
(select id,CONCAT(bftoken,token,\'"\',num,\'"\',substr(aftoken,num_length+1)) newurl
FROM (select id,token,
substr(b.url,1,a.hpos - 1) bftoken,
substr(b.url,a.hpos + length(a.token)) aftoken,
substr(b.url,a.hpos + length(a.token))+0 num,
length(substr(b.url,a.hpos + length(a.token))+0) num_length
from
(select id,token,LOCATE(token,url) hpos
from todd,(select \'width=\' token) w
WHERE LOCATE(CONCAT(token,\'"\'),url)=0) A
INNER JOIN todd B USING (id)) AA) AAA
INNER JOIN todd BBB USING (id)
SET BBB.url = AAA.newurl;
观察我运行这些并显示表内容时发生的情况:mysql> UPDATE
-> (select id,CONCAT(bftoken,token,\'"\',num,\'"\',substr(aftoken,num_length+1)) newurl
-> FROM (select id,token,
-> substr(b.url,1,a.hpos - 1) bftoken,
-> substr(b.url,a.hpos + length(a.token)) aftoken,
-> substr(b.url,a.hpos + length(a.token))+0 num,
-> length(substr(b.url,a.hpos + length(a.token))+0) num_length
-> from
-> (select id,token,LOCATE(token,url) hpos
-> from todd,(select \'height=\' token) w
-> WHERE LOCATE(CONCAT(token,\'"\'),url)=0) A
-> INNER JOIN todd B USING (id)) AA) AAA
-> INNER JOIN todd BBB USING (id)
-> SET BBB.url = AAA.newurl;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> UPDATE
-> (select id,CONCAT(bftoken,token,\'"\',num,\'"\',substr(aftoken,num_length+1)) newurl
-> FROM (select id,token,
-> substr(b.url,1,a.hpos - 1) bftoken,
-> substr(b.url,a.hpos + length(a.token)) aftoken,
-> substr(b.url,a.hpos + length(a.token))+0 num,
-> length(substr(b.url,a.hpos + length(a.token))+0) num_length
-> from
-> (select id,token,LOCATE(token,url) hpos
-> from todd,(select \'width=\' token) w
-> WHERE LOCATE(CONCAT(token,\'"\'),url)=0) A
-> INNER JOIN todd B USING (id)) AA) AAA
-> INNER JOIN todd BBB USING (id)
-> SET BBB.url = AAA.newurl;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from todd;
+----+-----------------------------------------------------------------------------------------+
| id | url |
+----+-----------------------------------------------------------------------------------------+
| 1 | <img height="319" alt="" src="http://www.example.com/images/myexample.jpg" width="496"> |
| 2 | <img height="329" alt="" src="http://www.example.com/images/myexample.jpg" width="130"> |
| 3 | <img height="339" alt="" src="http://www.example.com/images/myexample.jpg" width="206"> |
| 4 | <img height="349" alt="" src="http://www.example.com/images/myexample.jpg" width="498"> |
| 5 | <img height="359" alt="" src="http://www.example.com/images/myexample.jpg" width="499"> |
+----+-----------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)
mysql> select * from todd;
试试看!!!警告
如果发布真正的表结构,我将为该表编写正确的SQL
这是你10年的开始。。。
您可以在MySQL查询中使用REGEXP操作符,然后对返回的结果进行更新。
您需要的正则表达式如下所示:
width=([0-9]*)
因此,您的查询将类似于:SELECT * FROM table WHERE column REGEXP "width=([0-9]*)"
我之所以使用术语“类似”是因为我无法在数据库上测试它。进一步阅读:
希望这有帮助。我有一个网站,我目前正在建设一个奇怪的问题。我将尽可能多地提供细节,希望somebody 能够找出导致此问题的原因:)Overview of the problem每次我更新一个页面时,除了主页之外,我网站上的所有页面都会开始使用索引。php模板。即使页面分配了模板,它们仍然会恢复到索引。php模板。要修复它,我需要将永久链接更改为默认值,保存它,然后将它们放回我的自定义设置,即category/postname。虽然下次我更新页面时,它会再次发生。Plugins & Post Types我正在使