问题

我有一个带有 SET 字段的表：

CREATE TABLE `test` (`testset` set('one','two'))

有一些记录：

SELECT * FROM test;
+---------+
| testset |
+---------+
| one     |
| two     |
| one,two |
+---------+

如何选择每个记录的第一个元素？在这种情况下，可以这样做：

SELECT SUBSTRING_INDEX(testset, ',', 1) AS testset FROM test;
+---------+
| testset |
+---------+
| one     |
| two     |
| one     |
+---------+

当然，testset[0] 不起作用（我已经尝试过了），但我可以这样做。

英文:

I have a table with a SET field:

CREATE TABLE `test` (`testset` set(&#39;one&#39;,&#39;two&#39;) )

with a few records:

SELECT * FROM test;
+---------+
| testset |
+---------+
| one     |
| two     |
| one,two |
+---------+

How can I SELECT the first element of each record? So in this case, that would be

SELECT testset[0] FROM test;
+---------+
| testset |
+---------+
| one     |
| two     |
| one     |
+---------+

of course, testset[0] doesn't work (I tried), but how could I do this?

答案1

得分: 0

要查看它，请在您的表上尝试以下操作：

SELECT testset, SUBSTRING(testset, ',', 1) FROM test
    ORDER BY RAND() LIMIT 11;

请注意：这是原文的中文翻译。如果您需要任何进一步的帮助，请告诉我。

英文:

A MySQL SET is implemented as an ordered list.
It is stored in an integer, with the bottom bit representing the first item in the SET declaration.
INSERTing and SELECTing both act as if the value of the column as a commalist.
The implementation has ordering, the concept does not.
So... SUBSTRING_INDEX is the answer:

To see it, try this on your table:

SELECT testset, SUBSTRING(testset, &#39;,&#39;, 1) FROM test
    ORDER BY RAND()  LIMIT 11;

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。