提取“标签”内的 MySQL 数据使用正则表达式？
可能的重复：
在 mysql 中模拟正则表达式捕获组
美好的一天，
我存储了很多行数据在 MySQL 表中。典型的值可能看起来像这样：
::image-gallery::
::gallery-entry::images/01.jpg::/gallery-entry::
::/image-gallery::
有没有一种方法 - 通过正则表达式，我可以 a）从第一行提取术语图像库（它可以是任何短语，而不仅仅是图像库），然后提取中心线作为两个单独值，如下所示：
gallery-entry 然后是 images/01.jpg
可能有很多行 ::gallery-entry:: 值，并且它们也可以被称为任何东西。一个更完整的例子是：
::image-gallery::
::title::MY GALLERY::/title::
::date::2011-05-20::/date::
::gallery-entry::images/01.jpg::/gallery-entry::
::/image-gallery::
本质上我想要这些信息：上述情况下的内容类型（图像库），第一行和最后一行。然后我需要标题作为键值样式对，因此 title 作为键，MY GALLERY 作为值。然后，随后，我也需要此后的所有字段行（图库条目）作为键值对。
这是一个迁移脚本，其中旧系统中的数据将使用不同的语法迁移到新系统。
如果 MySQL select 语句不起作用，使用 PHP 脚本解析结果以提取数据是否会更容易？
任何和所有的帮助总是值得赞赏。
亲切的问候， 西蒙
Possible Duplicate:
Simulating regex capture groups in mysql
Good day,
I have many rows of data stored in a MySQL table. A typical value could look something like this:
::image-gallery::
::gallery-entry::images/01.jpg::/gallery-entry::
::/image-gallery::
Is there a way - by means of a regular expression that I can a) extract the term image gallery from the first line (it could be any phrase, not just image-gallery) and then extract the center line as two separate values like this:
gallery-entry and then images/01.jpg
There could be many lines of ::gallery-entry:: values, and they could be called anything as well. A more complete example would be:
::image-gallery::
::title::MY GALLERY::/title::
::date::2011-05-20::/date::
::gallery-entry::images/01.jpg::/gallery-entry::
::/image-gallery::
In essence I want this information: The content type (image-gallery) in the above case, first line and last line. Then I need the title as a key value style pair, so title as the key and MY GALLERY as the value. Then, subsequently, I would need all the rows of fields thereafter (gallery-entry) as key value pairs too.
This is for a migration script where data from an old system will be migrated over to a new system with different syntax.
If MySQL select statements would not work, would it be easier to parse the results with a PHP script for data extraction?
Any and all help is always appreciated.
Kind regards,
Simon
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
试试这个正则表达式：
标题：$1（匹配组 1）
画廊入场费：2 美元（匹配组 2）
Try this regex:
title: $1 (matching group 1)
gallery-entry: $2 (matching group 2)
来自 simulate-regex-capture-groups-in-mysql似乎没有一种方法可以轻松地使用 mysql 中的正则表达式捕获组。原因是 MySQL 本身不支持正则表达式中的捕获组。如果您想要该功能，您可以使用服务器端扩展（例如lib_mysqludf_preg）将该功能添加到MySQL。
最简单的方法是用SQL提取整列，然后用另一种语言（例如php）进行文本匹配。
在我的测试中 kenbritton 的正则表达式不起作用，但在其基础上构建以下正则表达式可以对您的测试数据起作用：
From simulating-regex-capture-groups-in-mysql there does not seem to be a way to easily capture groups with a regex in mysql. The reason is that MySQL does not natively support capture groups in a regex. If you want that functionality you can use a server side extension like lib_mysqludf_preg to add that capability to MySQL.
The easiest way is to extract the whole column with SQL and then do the text matching in another language (such as php).
In my tests kenbritton's regex didn't work, but building off of it the following regex worked on your test data: