MySQL:选择逗号分隔列表的第一个元素 [英] MySQL: select first element of a comma-separated list

问题描述

不幸的是，我有一个字段列，其中包含类似

Unfortunately, I have a field column which contains values like

• 4
• 12,3
• 8,5,6,7

我要写一条SELECT语句，其结果将是:

I'm going to write a SELECT statement, whose result would be:

• 4
• 12
• 8

由于MySQL不提供拆分"功能，我该如何在实践中做到这一点?

How can I do it in practice, since MySQL does not provide a "split" function?

推荐答案

使用MySQL的 SUBSTRING_INDEX 函数:

Use MySQL's SUBSTRING_INDEX function:

SELECT SUBSTRING_INDEX(field, ',', 1)

但是，将列表保留在以分隔符分隔的字符串中通常是对像MySQL这样的关系数据库管理系统的低效使用:(id, value)对表中，来>规范化您的数据结构.

However, keeping lists in delimiter-separated strings is generally an inefficient use of a relational database management system like MySQL: it is often better to normalise your data structure by keeping such lists in a separate table of (id, value) pairs.

这篇关于MySQL:选择逗号分隔列表的第一个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！