php 如何在 MySQL 数据库中获取枚举可能的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2350052/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get enum possible values in a MySQL database?
提问by Shamoon
I want to populate my dropdowns with enum possible values from a DB automatically. Is this possible in MySQL?
我想用来自数据库的枚举可能值自动填充我的下拉列表。这在 MySQL 中可能吗？
回答by Patrick Savalle
I have a codeigniter version for you. It also strips the quotes from the values.
我有一个 codeigniter 版本给你。它还从值中去除引号。
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;
}
回答by Matthew
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
- $options_array = str_getcsv($options, ',', "'") possibly would work (if you alter the substring to skip the opening and closing parentheses), or
- a regular expression
- 如果你很懒惰，直接将其 eval 到一个数组中（尽管 MySQL 的单引号转义可能不兼容），或者
- $options_array = str_getcsv($options, ',', "'") 可能会起作用（如果您更改子字符串以跳过左括号和右括号），或
- 正则表达式
回答by jasonbar
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 列的所有可能值，请使用 SHOW COLUMNS FROM tbl_name LIKE 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;
}
回答by Phreditor
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.
这与上面的很多类似，但为您提供没有循环的结果，并获得您真正想要的：一个用于生成选择选项的简单数组。
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] => 红色 [1] => 绿色 [2] => 蓝色)
回答by eggyal
This is one of Chris Komlenic's 8 Reasons Why MySQL's ENUM Data Type Is Evil:
这是 Chris Komlenic 的8 个为什么 MySQL 的 ENUM 数据类型是邪恶的原因之一：
4. Getting a list of distinct ENUM members is a pain.
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.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.
4. 获取不同 ENUM 成员的列表很痛苦。
一个非常常见的需求是用数据库中的可能值填充选择框或下拉列表。像这样：
选择颜色：
[ select box ]如果这些值存储在名为“colors”的引用表中，则您只需要：
SELECT * FROM colors...然后可以对其进行解析以动态生成下拉列表。您可以在参考表中添加或更改颜色，您的性感订单将自动更新。惊人的。现在考虑邪恶的 ENUM：如何提取成员列表？您可以在表中的 ENUM 列中查询 DISTINCT 值，但这只会返回表中实际使用和存在的值，而不一定是所有可能的值。您可以查询 INFORMATION_SCHEMA 并使用脚本语言从查询结果中解析它们，但这会不必要地复杂化。事实上，我不知道有什么优雅的、纯粹的 SQL 方法来提取 ENUM 列的成员列表。
回答by bashaus
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
回答by Scott Krelo
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 字符串，所以解析它！
回答by Сергей Алексанян
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);
回答by Anas Tiour
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;
}
回答by Андр?й Глущенко
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"
}
