MySQL:查询SET可用选项的列表

本文介绍了MySQL:查询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.