您好，我正在为一个我没有设计的数据库编写 MySQL 请求，目前无法真正更改，我有一个问题，我还没有测试过这个，所以它可能按设计工作，但我不确定。
因此，我正在搜索的表只有三列 id、标题和描述，但描述列实际上是以标准化方式存储的其他信息的列表（这是针对音乐元数据的，因此它看起来像这样）：
id |标题 |描述
1 |通往天堂的阶梯| Artist=led_zeppelin, Genre=rock, album=led_zeppelin_iv
我想要做的是编写查询，该查询将选择包含字符串 zeppelin 的艺术家的所有歌曲
我最初想到的是
"SELECT * FROM table WHERE description LIKE % Artist=%zeppelin%"
虽然我相信如果描述中存在该名称，这应该可行，但我认为问题出在我想要找到所有艺术家的场景中包含摇滚它看起来像这样
"SELECT * FROM table WHERE description LIKE %artist=%rock%"
我的第一个问题是这会选择该行，因为摇滚位于 Artist= 之后的行中，正确吗？
我的第二个问题是这能解决问题吗？
"SELECT * FROM table WHERE description LIKE %artist=%rock% AND WHERE description NOT LIKE %artist=%,%rock%"
我的想法是，如果我省略带有 , (分隔符）在艺术家=和比较标签之间，它不会选择上面的行。我的潜在问题是，如果分隔符位于艺术家姓名中，则会失败，但我应该能够通过选择不在任何字段值中的分隔符并强制保留此属性来解决此问题。
对于任何格式问题也表示歉意，这是我第一次尝试编写问题。
谢谢，
吐温249
Hi I'm writing MySQL requests for a database that I didn't design, and cannot really change at this point, and I had a question, I have not tested this so it might work as designed but I'm not sure.
So the table I'm searching only have three columns id, title, and description but the description column is actually a list of other information stored in a standardized way (this is for a music metadata so it would look something like this):
id | title | description
1 | stairway_to_heaven | artist=led_zeppelin, genre=rock, album=led_zeppelin_iv
What I want to do is write query that will select all the songs with artist containing the string zeppelin
What I initially came up with is
"SELECT * FROM table WHERE description LIKE %artist=%zeppelin%"
While I believe this should work if the name is present in the description what I believe to be the problem is in the scenario where I want to find all the artists that contain rock it would look like this
"SELECT * FROM table WHERE description LIKE %artist=%rock%"
My first question is this would select the row because rock is in the row after artist= correct?
My second question is would this fix the issue?
"SELECT * FROM table WHERE description LIKE %artist=%rock% AND WHERE description NOT LIKE %artist=%,%rock%"
My thought was that if I omit the Strings that have a , (the delimiter) in between the artist= and the comparison tag it would not choose the above row. My potential problem is that if the delimiter is in the artist name it would fail but I should be able to solve this by choosing a delimiter that isn't in any of the field values and enforcing that this property holds.
Also sorry about any formatting issues this is my first attempt at writing a question.
Thanks,
twain249
发布评论
评论（1）
您有使用正则表达式的经验吗？如果是，您可以在查询中使用它们来处理类似的情况。请参阅此处的一些示例： http://dev.mysql.com/doc/ refman/5.0/en/regexp.html
或者，您可以使用此处定义的“split”函数：http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/
Do you have experience with Regular expressions? If yes, you can use them in your query to deal with cases like this. See some examples here: http://dev.mysql.com/doc/refman/5.0/en/regexp.html
Alternatively, you can use a "split" function as defined here: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/