MySQL:查询SET可用选项的列表 [英] MySQL: Query for list of available options for SET

问题描述

在特定表中，存在具有特定合法值的SET类型的字段:

In particular table there exists a field of SET type with specific legal values:

    personType SET('CUSTOMER','SUPPLIER','EMPLOYEE', 'CONTRACTOR') NOT NULL

有什么方法可以查询MySQL以获取有效值列表吗?在MySQL解释器中，我将只运行DESCRIBE someTable;，但是，如果有一种更直接的方法可以在无需大量分析的情况下以编程方式使用它，那将是很好的.

Is there any way to query MySQL to get a list of the valid values? In the MySQL interpreter I would just run DESCRIBE someTable; however if there is a more direct method that one could use programmatically without lots of parsing it would be nice.

谢谢.

推荐答案

现在，这简直吓死了，但它仅适用于MySQL并且可以正常工作！

Now, this simply freaks out, but it is MySQL-only and it works!

SELECT TRIM("'" FROM SUBSTRING_INDEX(SUBSTRING_INDEX(
   (SELECT TRIM(')' FROM SUBSTR(column_type, 5)) FROM information_schema.columns 
    WHERE table_name = 'some_table' AND column_name = 'some_column'),
',', @r:=@r+1), ',', -1)) AS item
FROM (SELECT @r:=0) deriv1,
(SELECT ID FROM information_schema.COLLATIONS) deriv2
HAVING @r <= 
   (SELECT LENGTH(column_type) - LENGTH(REPLACE(column_type, ',', ''))
    FROM information_schema.columns
    WHERE table_name = 'some_table' AND column_name = 'some_column');

只需将"some_table"和"some_column"替换为您特定的表/列，然后看一下魔术吧！

Just replace "some_table" and "some_column" for your specific table/column, and see the magic!

您会看到"information_schema.COLLATIONS"的怪异用法-这是因为我们在那里需要一个表-任何表-包含至少N行，其中N是集合中元素的数量.

You will see a weird usage of "information_schema.COLLATIONS" - this is because we need a table there - any table - containing at least N rows, where N is the number of elements in your set.

这篇关于MySQL:查询SET可用选项的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！