如何在MySQL数据库中获得enum可能的值?

I want to populate my dropdowns with enum possible values from a DB automatically. Is this possible in MySQL?

我想用一个DB的enum可能值自动填充下拉列表。这在MySQL中是可行的吗?

21 个解决方案

#1

I have a codeigniter version for you. It also strips the quotes from the values.

我有一个代码点火器版本给你。它还从值中除去引号。

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

#2

You can get the values by querying it like this:

你可以通过这样的查询来获取值:

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

From there you'll need to convert it into an array:

从那里你需要把它转换成一个数组:

eval that directly into an array if you're lazy (although MySQL's single quote escape might be incompatible), or
如果您懒惰，可以直接在数组中eval它(尽管MySQL的单引号转义可能不兼容)，或者
$options_array = str_getcsv($options, ',', "'") possibly would work (if you alter the substring to skip the opening and closing parentheses), or
$options_array = str_getcsv($options， '，'， "'")可能会工作(如果您更改子字符串以跳过开始和结束括号)，或者
a regular expression
一个正则表达式

#3

MySQL Reference

MySQL参考

If you want to determine all possible values for an ENUM column, use SHOW COLUMNS FROM tbl_name LIKE enum_col and parse the ENUM definition in the Type column of the output.

如果要确定ENUM列的所有可能值，请使用tbl_name中的SHOW列(如enum_col)，并在输出的Type列中解析ENUM定义。

You would want something like:

你会想要:

$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]);

This will give you the quoted values. MySQL always returns these enclosed in single quotes. A single quote in the value is escaped by a single quote. You can probably safely call trim($val, "'") on each of the array elements. You'll want to convert '' into just '.

这将给出所引用的值。MySQL总是返回这些包含在单引号中的。值中的一个引用被一个引用转义。您可以在每个数组元素上安全地调用trim($val)。你会想把它转换成。

The following will return $trimmedvals array items without quotes:

以下将返回$trimmedvals数组项没有引号:

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

#4

This is like a lot of the above, but gives you the result without loops, AND gets you want you really want: a simple array for generating select options.

这类似于上面的许多内容，但是提供了没有循环的结果，并得到了您真正想要的:用于生成select选项的简单数组。

BONUS: It works for SET as well as ENUM field types.

附加:它适用于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: Array ( [0] => red [1] => green [2] => blue )

$option_array:数组([0]=> red[1] =>绿色[2]=> blue)

#5

You can parse the string as though it was a CSV (Comma Separated Value) string. PHP has a great build-in function called str_getcsv which converts a CSV string to an array.

可以将字符串解析为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);

This should give you something similar to the following:

这应该会给你一些类似以下的东西:

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

This method also allows you to have single quotes in your strings (notice the use of two single quotes):

此方法还允许在字符串中使用单引号(注意使用两个单引号):

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

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

For more information on the str_getcsv function, check the PHP manual: http://uk.php.net/manual/en/function.str-getcsv.php

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

#6

This is one of Chris Komlenic's 8 Reasons Why MySQL's ENUM Data Type Is Evil:

这就是为什么MySQL的ENUM数据类型是邪恶的8个原因之一:

4. Getting a list of distinct ENUM members is a pain.

4所示。获得一个不同的ENUM成员列表是一件痛苦的事情。

A very common need is to populate a select-box or drop down list with possible values from the database. Like this:

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

Select color:

选择颜色:

[ select box ]

(选择框)

If these values are stored in a reference table named 'colors', all you need is: SELECT * FROM colors ...which can then be parsed out to dynamically generate the drop down list. You can add or change the colors in the reference table, and your sexy order forms will automatically be updated. Awesome.

如果这些值存储在一个名为“colors”的引用表中，那么您所需要的就是:SELECT * FROM colors…然后可以对其进行解析以动态生成下拉列表。您可以添加或更改参考表中的颜色，您的性感订单将自动更新。太棒了。

Now consider the evil ENUM: how do you extract the member list? You could query the ENUM column in your table for DISTINCT values but that will only return values that are actually used and present in the table, not necessarily all possible values. You can query INFORMATION_SCHEMA and parse them out of the query result with a scripting language, but that's unnecessarily complicated. In fact, I don't know of any elegant, purely SQL way to extract the member list of an ENUM column.

现在考虑一下邪恶的ENUM:如何提取成员列表?您可以查询表中的ENUM列以获取不同的值，但这将只返回表中实际使用和显示的值，而不一定是所有可能的值。您可以使用脚本语言查询INFORMATION_SCHEMA并从查询结果中解析它们，但这是不必要的复杂。实际上，我不知道有什么优雅的纯SQL方法可以提取ENUM列的成员列表。

#7

A more up to date way of doing it, this worked for me:

一种更现代的方式，这对我很有用:

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;
}

Ultimately, the enum values when separated from "enum()" is just a CSV string, so parse it as such!

最终，当枚举值与“enum()”分离时，它只是一个CSV字符串，因此要将其解析为这样!

#8

here is for mysqli

这里是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);

#9

I simply want to add to what jasonbar says, when querying like:

我只是想补充一下jasonbar说的，当查询的时候:

SHOW columns FROM table

If you get the result out as an array it will look like this:

如果你以数组的形式得到结果，结果会是这样的:

array([0],[Field],[1],[Type],[2],[Null],[3],[Key],[4],[Default],[5],[Extra])

Where [n] and [text] give the same value.
Not really told in any documentation I have found. Simply good to know what else is there.

其中[n]和[text]给出相同的值。在我找到的任何文件中都没有提到。知道还有什么很好。

#10

$row = db_fetch_object($result);
     if($row){
     $type = $row->Type;
     preg_match_all("/'([^']+)'/", $type, $matches,PREG_PATTERN_ORDER );
     return $matches[1];


}

#11

try this

试试这个

describe table columnname

gives you all the information about that column in that table;

给出关于该表中该列的所有信息;

#12

Here is the same function given by Patrick Savalle adapted for the framework Laravel

这是Patrick Savalle为Laravel框架提供的相同功能

function get_enum_values($table, $field)
{

   $test=DB::select(DB::raw("show columns from {$table} where field = '{$field}'"));

   preg_match('/^enum\((.*)\)$/', $test[0]->Type, $matches);
   foreach( explode(',', $matches[1]) as $value )
   {
       $enum[] = trim( $value, "'" );   
   }

   return $enum;

}

#13

Codeigniter adapting version as method of some model:

Codeigniter改编版本为某种模式的方法:

public function enum_values($table_name, $field_name)
{
    $query = $this->db->query("SHOW COLUMNS FROM `{$table_name}` LIKE '{$field_name}'");

    if(!$query->num_rows()) return array();
    preg_match_all('~\'([^\']*)\'~', $query->row('Type'), $matches);

    return $matches[1];
}

Result:

结果:

array(2) {
    [0]=> string(13) "administrator"
    [1]=> string(8) "customer"
}

#14

You can use this syntax for get enum possible values in MySQL QUERY :

在MySQL查询中，可以使用这种语法获取enum可能的值:

$syntax = "SELECT COLUMN_TYPY FROM information_schema.`COLUMNS` 
WHERE TABLE_NAME = '{$THE_TABLE_NAME}' 
AND COLUMN_NAME = '{$THE_COLUMN_OF_TABLE}'";

and you get value, example : enum('Male','Female')

你会得到值，比如enum('Male'，'Female')

this is example sytax php:

这是sytax php示例:

<?php
function ($table,$colm){

// mysql query.
$syntax = mysql_query("SELECT COLUMN_TYPY FROM information_schema.`COLUMNS` 
WHERE TABLE_NAME = '$table' AND COLUMN_NAME ='$colm'");

if (!mysql_error()){
 //Get a array possible values from table and colm.
 $array_string = mysql_fetch_array($syntax);

    //Remove part string
    $string = str_replace("'", "", $array_string['COLUMN_TYPE']);
    $string = str_replace(')', "", $string);
    $string = explode(",",substr(5,$string));
}else{
    $string = "error mysql :".mysql_error();
}
// Values is (Examples) Male,Female,Other
return $string;
}
?>

#15

For Laravel this worked:

Laravel这个工作:

$result = DB::select("SHOW COLUMNS FROM `table_name` LIKE 'status';");
$regex = "/'(.*?)'/";
preg_match_all( $regex , $result[0]->Type, $enum_array );
$enum_fields = $enum_array[1];
echo "<pre>";
print_r($enum_fields);

Output:

输出:

Array
(
[0] => Requested
[1] => Call Back
[2] => Busy
[3] => Not Reachable
[4] => Not Responding
)

#16

All of you use some strange and complex regex patterns x)

所有人都使用一些奇怪而复杂的regex模式x)

Here's my solution without preg_match :

这是我的解决方案，没有preg_match:

function getEnumTypes($table, $field) {
    $query = $this->db->prepare("SHOW COLUMNS FROM $table WHERE Field = ?");
    try {$query->execute(array($field));} catch (Exception $e) {error_log($e->getMessage());}
    $types = $query->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_UNIQUE, 1)[$field];
    return explode("','", trim($types, "enum()'"));
}

#17

this will work for me:

这对我来说行得通:

SELECT REPLACE(SUBSTRING(COLUMN_TYPE,6,(LENGTH(COLUMN_TYPE)-6)),"'","")
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='__TABLE_SCHEMA__' 
AND TABLE_NAME='__TABLE_NAME__'
AND COLUMN_NAME='__COLUMN_NAME__'

and then

然后

explode(',', $data)

#18

The problem with every other answer in this thread is that none of them properly parse all special cases of the strings within the enum.

这个线程中所有其他答案的问题是，它们都没有正确地解析enum中所有字符串的特殊情况。

The biggest special case character that was throwing me for a loop was single quotes, as they are encoded themselves as 2 single quotes together! So, for example, an enum with the value 'a' is encoded as enum('''a'''). Horrible, right?

最大的特殊情况字符是单引号，因为他们自己编码为两个单引号在一起!因此，例如，值为“a”的枚举被编码为enum(“a”)。可怕的,对吧?

Well, the solution is to use MySQL to parse the data for you!

解决方案是使用MySQL为您解析数据!

Since everyone else is using PHP in this thread, that is what I will use. Following is the full code. I will explain it after. The parameter $FullEnumString will hold the entire enum string, extracted from whatever method you want to use from all the other answers. RunQuery() and FetchRow() (non associative) are stand ins for your favorite DB access methods.

因为每个人都在这个线程中使用PHP，所以我将使用它。以下是完整的代码。我以后再解释。参数$FullEnumString将保存整个enum字符串，从您想要使用的所有其他答案的任何方法中提取。RunQuery()和FetchRow()(非关联)是您最喜欢的DB访问方法的缩写。

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

preg_match('/^enum$(.*)$$/iD', $FullEnumString, $Matches) confirms that the enum value matches what we expect, which is to say, "enum(".$STUFF.")" (with nothing before or after). If the preg_match fails, NULL is returned.

preg_match(' / ^ enum $(. *)$$ / iD ',FullEnumString美元,$ Matches)证实,枚举值匹配我们期望的,也就是说,“枚举(美元的东西。)”(之前和之后都没有)。如果preg_match失败，则返回NULL。

This preg_match also stores the list of strings, escaped in weird SQL syntax, in $Matches[1]. So next, we want to be able to get the real data out of that. So you just run "SELECT ".$Matches[1], and you have a full list of the strings in your first record!

preg_match还将字符串列表存储在$Matches[1]中，这些字符串以奇怪的SQL语法转义。接下来，我们想要得到真实的数据。你只需要运行"SELECT "。$匹配[1]，您的第一个记录中有完整的字符串列表!

So just pull out that record with a FetchRow(RunQuery(...)) and you’re done.

所以只要用FetchRow(RunQuery(…))提取那个记录，就完成了。

If you wanted to do this entire thing in SQL, you could use the following

如果您想要在SQL中执行整个操作，可以使用以下命令

SET @TableName='your_table_name', @ColName='your_col_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)));
PREPARE stmt FROM @Q;
EXECUTE stmt;

P.S. To preempt anyone from saying something about it, no, I do not believe this method can lead to SQL injection.

另外，为了避免任何人对它发表评论，不，我不认为这种方法会导致SQL注入。

#19

I get enum values in this way:

我以这种方式得到枚举值:

SELECT COLUMN_TYPE 
FROM information_schema.`COLUMNS` 
WHERE TABLE_NAME = 'tableName' 
     AND COLUMN_NAME = 'columnName';

Running this sql I have get : enum('BDBL','AB Bank')

运行我得到的sql: enum('BDBL'，'AB Bank')

then I have filtered just value using following code :

然后我用下面的代码过滤了值:

preg_match("/^enum\(\'(.*)\'\)$/", $type, $matches);
$enum = explode("','", $matches[1]);
var_dump($enum) ;

Out put :

把:

array(2) { [0]=> string(4) "BDBL" [1]=> string(7) "AB Bank" }

数组(2){[0]= >字符串(4)“BDBL”[1]= >字符串(7)“AB银行”}

#20

To fetch the list of possible values has been well documented, but expanding on another answer that returned the values in parenthesis, I wanted to strip them out leaving me with a comma separated list that would then allow me to use an explode type function whenever I needed to get an array.

获取可能值的列表已经有据可查,但扩大括号中的值返回另一个答案,我想带他们离开我的逗号分隔列表就会允许我使用一个爆炸类型函数每当我需要得到一个数组。

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

The SUBSTRING now starts at the 6th character and uses a length which is 6 characters shorter than the total, removing the trailing parenthesis.

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

#21

For PHP 5.6+

PHP 5.6 +

$mysqli = new mysqli("example.com","username","password","database");
$result = $mysqli->query("SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table_name' AND COLUMN_NAME='column_name'");
$row = $result->fetch_assoc();
var_dump($row);

#1