如何在MySQL数据库中获取枚举类型的可能值？

Question

如何在MySQL数据库中获取枚举类型的可能值？

phpmysql

114

我想要从数据库自动获取枚举值，然后将其填充到下拉框中。在MySQL中是否可以实现这个功能？

- Shamoon

30个回答

61

你可以像这样查询并获取值：

SELECT SUBSTRING(COLUMN_TYPE,5)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='databasename' 
    AND TABLE_NAME='tablename'
    AND COLUMN_NAME='columnname'

从那里开始，你需要将它转换为数组:

如果你懒的话，可以直接将其评估为一个数组(eval)，尽管MySQL的单引号转义可能不兼容, 或者
$options_array = str_getcsv($options, ',', "'") 可能会起作用（如果你修改子字符串来跳过开放和关闭括号），或者
正则表达式。

- Matthew

37

MySQL参考手册

如果您想确定ENUM列的所有可能值，请使用SHOW COLUMNS FROM tbl_name LIKE enum_col，并解析输出中Type列中的ENUM定义。

您需要类似以下的内容:

$sql = "SHOW COLUMNS FROM `table` LIKE 'column'";
$result = $db->query($sql);
$row = $result->fetchRow();
$type = $row['Type'];
preg_match('/enum\((.*)\)$/', $type, $matches);
$vals = explode(',', $matches[1]);

这将给你带来带引号的值。MySQL总是返回这些值用单引号括起来。值中的单引号通过一个单引号进行转义。您可以在每个数组元素上安全地调用trim($val, "'")。您需要将''转换为'。

以下内容将返回不带引号的$trimmedvals数组项：

$trimmedvals = array();
foreach($vals as $key => $value) {
$value=trim($value, "'");
$trimmedvals[] = $value;
}

- jasonbar

13

这与上面的很多类似，但可以在无需循环的情况下给出结果，并且为生成选择选项提供了一个简单的数组。

奖励：它适用于SET以及ENUM字段类型。

$result = $db->query("SHOW COLUMNS FROM table LIKE 'column'");
if ($result) {
    $option_array = explode("','",preg_replace("/(enum|set)\('(.+?)'\)/","\\2", $result[0]->Type));
}

$option_array：数组 ( [0] => 红色 [1] => 绿色 [2] => 蓝色 )

- Phreditor

11

您可以将字符串解析为CSV格式的字符串。PHP有一个很棒的内置函数称为str_getcsv，它可以将CSV字符串转换为数组。

// This is an example to test with
$enum_or_set = "'blond','brunette','redhead'";

// Here is the parser
$options = str_getcsv($enum_or_set, ',', "'");

// Output the value
print_r($options);

这应该会给你类似于以下的结果：

Array
(
    [0] => blond
    [1] => brunette
    [2] => redhead
)

这种方法还允许您在字符串中使用单引号（请注意使用两个单引号）：

$enum_or_set = "'blond','brunette','red''head'";

Array
(
    [0] => blond
    [1] => brunette
    [2] => red'head
)

如需了解更多关于 str_getcsv 函数的信息，请参阅 PHP 手册： http://uk.php.net/manual/en/function.str-getcsv.php

- bashaus

1

我给出的例子并没有展示如何从数据库中获取字段信息，但是这个页面上有很多其他的例子可以向你展示如何做到这一点。 - bashaus

2

str_getcsv 只在 PHP 5 >= 5.3.0 版本以上可用，如果您想在早期版本中使用此功能，可以包含此文件。 - Steve

1

这应该是正确的处理方式，因为它考虑了字符串内部的转义引号和逗号。 - Kristoffer Sall-Storgaard

1

绝妙的解决方案，节省时间 ;) - John

对我来说，它没有正确处理反斜杠（\）。我不得不手动取消转义。 - undefined

10

这是Chris Komlenic的《MySQL枚举数据类型的八大问题》之一：

4. 获取枚举成员列表很麻烦。

一个非常常见的需求是从数据库中填充选择框或下拉列表的可能值。像这样：

选择颜色：

[ 选择框 ]

如果这些值存储在一个名为“colors”的引用表中，你只需要：SELECT * FROM colors...就可以解析出动态生成的下拉列表。你可以在引用表中添加或更改颜色，你性感的订单表格就会自动更新。太棒了。

现在考虑一下邪恶的ENUM：如何提取成员列表？你可以查询表中ENUM列的DISTINCT值，但这只会返回实际使用和存在于表中的值，不一定是所有可能的值。你可以查询INFORMATION_SCHEMA，并用脚本语言从查询结果中解析它们，但这是不必要的复杂操作。事实上，我不知道任何优雅、纯SQL的方法来提取ENUM列的成员列表。

- eggyal

7

更现代的方法是这样做，这对我有效：

function enum_to_array($table, $field) {    
    $query = "SHOW FIELDS FROM `{$table}` LIKE '{$field}'";
    $result = $db->query($sql);
    $row = $result->fetchRow();
    preg_match('#^enum\((.*?)\)$#ism', $row['Type'], $matches);
    $enum = str_getcsv($matches[1], ",", "'");
    return $enum;
}

最终，当将枚举值从"enum()"中分离出来时，它只是一个CSV字符串，因此请将其解析为这样的字符串！

- Scott Krelo

5

这里是关于mysqli的内容。

function get_enum_values($mysqli, $table, $field )
{
    $type = $mysqli->query("SHOW COLUMNS FROM {$table} WHERE Field = '{$field}'")->fetch_array(MYSQLI_ASSOC)['Type'];
    preg_match("/^enum\(\'(.*)\'\)$/", $type, $matches);
    $enum = explode("','", $matches[1]);
    return $enum;
}
$deltypevals = get_enum_values($mysqli, 'orders', 'deltype');
var_dump ($deltypevals);

- Сергей Алексанян

4

在这个线程中，其他回答的问题在于它们都没有正确解析枚举值字符串的所有特殊情况。最大的特殊情况字符是单引号，因为它们本身被编码为2个连续的单引号！所以，例如，一个值为'a'的枚举被编码为enum('''a''')。很可怕，对吧？

那么，解决方案就是使用MySQL来为您解析数据！由于其他人都在使用PHP，在这个线程中，我也会使用它。以下是完整的代码。参数$FullEnumString将保存从其他答案提供的任何方法中提取出的完整枚举字符串。RunQuery()和FetchRow()（非关联型）是您喜欢使用的数据库访问方法的替代品。

function GetDataFromEnum($FullEnumString)
{
    if(!preg_match('/^enum\((.*)\)$/iD', $FullEnumString, $Matches))
        return null;
    return FetchRow(RunQuery('SELECT '.$Matches[1]));
}

preg_match('/^enum$(.*)$$/iD', $FullEnumString, $Matches) 用于确认枚举值是否符合我们所期望的，也就是说，"enum(".$STUFF.")"（前后没有其他内容）。如果 preg_match 失败，则返回 NULL。

这个 preg_match 还将以奇怪的 SQL 语法转义的字符串列表存储在 $Matches[1] 中。因此，下一步，我们想要能够从中获取真正的数据。只需运行 "SELECT ".$Matches[1]，然后您就可以在第一条记录中获得所有字符串的完整列表！

所以只需要使用 FetchRow(RunQuery(...)) 拉出那条记录，然后您就完成了。

如果您想要在 SQL 中执行此操作，则可以使用以下方法：

SET @TableName='your_table_name', @ColName='your_col_name', @DBName='your_database_name';
SET @Q=(SELECT CONCAT('SELECT ', (SELECT SUBSTR(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE)-6) FROM information_schema.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME=@ColName AND TABLE_SCHEMA=@DBName)));
PREPARE stmt FROM @Q;
EXECUTE stmt;

补充一句，为了防止有人提出质疑，我并不认为这种方法会导致SQL注入。

- Dakusan

很棒的解决方案！当然，这不可能是SQL注入，因为我们正在从自己的数据库模式中获取值。 - terales

感谢您的方法。我在数组中得到了重复的值，如下所示：{"building_regulations":"building_regulations","0":"building_regulations","list_of_owners":"list_of_owners","1":"list_of_owners"}。有什么想法吗？（在phpmyadmin中运行良好，但使用POD对象运行时会出现此结果。） - Guney Ozsan

有三种从php中的mysql查询中提取行值的方法。mysql_fetch_assoc使用正确的键提取值，mysql_fetch_row使用数字键提取值，而mysql_fetch_array可以将值作为一个或两者都提取。看起来你的pdo正在使用mysql_fetch_array进行此操作。在提取期间，应该有一种方法告诉它不要这样做。 - Dakusan

太棒了！这就是让MySQL完成所有艰苦工作的方法。 - Spencer Williams

1

正如我在此帖的另一个答案中已经提到的，如果你有多个数据库，你必须指定TABLE_SCHEMA。因此，第一句话应该是 SET @TableName='your_table_name', @ColName='your_col_name', @DBName='your_database_name'; 然后将WHERE子句扩展为AND `TABLE_SCHEMA=@DBName，即使有多个数据库，它也能正常工作。 - Alexander Behling

显示剩余2条评论

4

获取可能值的列表已经有很好的文档记录，但是在扩展另一个返回带括号值的答案的基础上，我想去掉它们，只留下逗号分隔的列表，这样每当需要获取数组时就可以使用类似于“explode”的函数。

SELECT
    SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6) AS val
FROM
    information_schema.COLUMNS
WHERE
    TABLE_NAME = 'articles'
AND
    COLUMN_NAME = 'status'

SUBSTRING现在从第6个字符开始，使用的长度比总长度短6个字符，并删除尾部的括号。

- cchana

如果您有多个数据库，还必须指定TABLE_SCHEMA。 - Alexander Behling

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Patrick Savalle · Accepted Answer

112

这个版本还会从值中去掉引号。

function get_enum_values( $table, $field )
{
    $type = fetchRowFromDB( "SHOW COLUMNS FROM {$table} WHERE Field = '{$field}'" )->Type;
    preg_match("/^enum\(\'(.*)\'\)$/", $type, $matches);
    $enum = explode("','", $matches[1]);
    return $enum;
}

- Patrick Savalle

33

如果枚举值本身包含逗号，那么这个解决方案将会失效。 - Fo.

3

这个解决方案适用于CodeIgniter，链接是https://github.com/thiswolf/codeigniter-enum-select-boxes。 - You Know Nothing Jon Snow

3

PHP版本： $type = $this->mysql->select("SHOW COLUMNS FROM $table WHERE Field = '$field'")[0]["Type"]; - Alessandro.Vegna

2

我已经添加了一个答案，其中包含一种完全可靠的方法来提取所有枚举值，无论其中包含什么字符。 - Dakusan

为了使用PDO，我需要将第一行替换为

$sth = $this->db->prepare( "SHOW COLUMNS FROM {$table} WHERE Field = '{$field}'" );         $sth->execute();         $type = $sth->fetch()->Type;

。 - Utmost Creator

显示剩余3条评论