用引号将img标记中的数字括起来的SQL查询

时间：2012-04-12 作者：Todd Lahman

我有相当多的老帖子，其中包含一个高度和宽度的img标签，数字周围没有双引号。例如：

<img height=319 alt="" src="http://www.example.com/images/myexample.jpg" width=496>

我不知道如何编写MySQL查询来查找高度和宽度数字，并用双引号将其括起来。

虽然MySQL查询是首选，但如果这不可能，那么也许有人可以建议我使用一个正则表达式和一个PHP脚本来解决这个问题。

2 个回复

SO网友:RolandoMySQLDBA

你一定会喜欢这个的

首先，这里是一个加载了数据的示例表：

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

SO网友:Alex Thomas

这是你10年的开始。。。

您可以在MySQL查询中使用REGEXP操作符，然后对返回的结果进行更新。

您需要的正则表达式如下所示：

width=([0-9]*)

因此，您的查询将类似于：

SELECT * FROM table WHERE column REGEXP "width=([0-9]*)"

我之所以使用术语“类似”是因为我无法在数据库上测试它。

进一步阅读：

http://www.regular-expressions.info/mysql.html

希望这有帮助。

结束

小码农CODE

用引号将img标记中的数字括起来的SQL查询

相关推荐

更新页面后，站点上的所有页面都使用index.php模板