MySQL Mysql可以拆分列吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1096679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can Mysql Split a column?
提问by ZA.
I have a column that has comma separated data:
我有一列包含逗号分隔的数据：
1,2,3
3,2,1
4,5,6
5,5,5
I'm trying to run a search that would query each value of the CSV string individually.
我正在尝试运行一个搜索来分别查询 CSV 字符串的每个值。
0<first<5 and 1<second<3 and 2<third<4
I get that I could return all queries and split it myself and compare it myself. I'm curious if there is a way to do this so mysql does that processing work. Thanks!
我知道我可以返回所有查询并自己拆分并自己比较。我很好奇是否有办法做到这一点，以便 mysql 进行处理工作。谢谢！
回答by raj karthy
Use
用
substring_index(`column`,',',1) ==> first value
substring_index(substring_index(`column`,',',-2),',',1)=> second value
substring_index(substring_index(`column`,',',-1),',',1)=> third value
in your where clause.
在您的 where 子句中。
SELECT * FROM `table`
WHERE
substring_index(`column`,',',1)<0
AND
substring_index(`column`,',',1)>5
回答by Oleksiy Muzalyev
It seems to work:
它似乎有效：
substring_index ( substring_index ( context,',',1 ), ',', -1) )
substring_index ( substring_index ( context,',',2 ), ',', -1) )
substring_index ( substring_index ( context,',',3 ), ',', -1) )
substring_index ( substring_index ( context,',',4 ), ',', -1) )
it means 1st value, 2nd, 3rd, etc.
这意味着第一个值，第二个，第三个等。
Explanation:
解释：
The inner substring_indexreturns the first n values that are comma separated. So if your original string is "34,7,23,89", substring_index( context,',', 3)returns "34,7,23".
The outer substring_indextakes the value returned by the inner substring_indexand the -1allows you to take the last value. So you get "23" from the "34,7,23".
Instead of -1if you specify -2, you'll get "7,23", because it took the last two values.
内部substring_index返回以逗号分隔的前 n 个值。因此，如果您的原始字符串是“34,7,23,89”，则substring_index( context,',', 3)返回“34,7,23”。
外部substring_index采用内部返回的值，substring_index并-1允许您采用最后一个值。所以你从“34,7,23”中得到“23”。
而不是-1如果你指定-2，你会得到“7,23”，因为它采用了最后两个值。
Example:
例子：
select * from MyTable where substring_index(substring_index(prices,',',1),',',-1)=3382;
Here, pricesis the name of a column in MyTable.
这里，prices是MyTable.
回答by runixo
Usually substring_indexdoes what you want:
通常substring_index做你想要的：
mysql> select substring_index("[email protected]","@",-1);
+-----------------------------------------+
| substring_index("[email protected]","@",-1) |
+-----------------------------------------+
| gmail.com |
+-----------------------------------------+
1 row in set (0.00 sec)
回答by Damo
You may get what you want by using the MySQL REGEXP or LIKE.
您可以通过使用 MySQL REGEXP 或 LIKE 获得您想要的东西。
See the MySQL Docs on Pattern Matching
回答by Merijn Vogel
As an addendum to this, I've strings of the form: Some words 303
作为对此的补充，我有以下形式的字符串：Some words 303
where I'd like to split off the numerical part from the tail of the string. This seems to point to a possible solution:
我想从字符串的尾部分离数字部分。这似乎指向一个可能的解决方案：
http://lists.mysql.com/mysql/222421
http://lists.mysql.com/mysql/222421
The problem however, is that you only get the answer "yes, it matches", and not the start index of the regexp match.
然而，问题是您只能得到“是的，它匹配”的答案，而不是正则表达式匹配的起始索引。
回答by KCD
Here is another variant I posted on related question. The REGEXcheck to see if you are out of bounds is useful, so for a table column you would put it in the where clause.
这是我在相关问题上发布的另一个变体。该REGEX检查，看看，如果你是出界是很有用的，所以对于一个表列你会把它的where子句。
SET @Array = 'one,two,three,four';
SET @ArrayIndex = 2;
SELECT CASE
WHEN @Array REGEXP CONCAT('((,).*){',@ArrayIndex,'}')
THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@Array,',',@ArrayIndex+1),',',-1)
ELSE NULL
END AS Result;
SUBSTRING_INDEX(string, delim, n)returns the first nSUBSTRING_INDEX(string, delim, -1)returns the last onlyREGEXP '((delim).*){n}'checks if there are n delimiters (i.e. you are in bounds)
SUBSTRING_INDEX(string, delim, n)返回第一个 nSUBSTRING_INDEX(string, delim, -1)只返回最后一个REGEXP '((delim).*){n}'检查是否有 n 个分隔符（即您在边界内）
回答by user2257559
It's working..
它的工作..
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(col,'1', 1), '2', 1), '3', 1), '4', 1), '5', 1), '6', 1)
, '7', 1), '8', 1), '9', 1), '0', 1) as new_col
FROM table_name group by new_col;
