How to Get Enum Possible Values in a MySQL Database

How can I get enum possible values in a MySQL database?

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

How can I get enum possible values in a MySQL database using php?

You should parse information from information_schema.columns table -

SELECT
  column_type
FROM
  information_schema.columns
WHERE
  table_schema = 'your_schema' AND table_name = 'your_table' AND column_name = 'your_column'

...another query -

SELECT
  TRIM(TRAILING ')' FROM TRIM(LEADING '(' FROM TRIM(LEADING 'enum' FROM column_type))) column_type
FROM
  information_schema.columns
WHERE
  table_schema = 'your_schema' AND table_name = 'your_table' AND column_name = 'your_column';

There will be something like this - enum('01','02','03'). Parse this string in the php-application.

Getting the values of a MySQL enum using only SQL

While I would agree about not using enums most of the time, it is possible in a single SQL statement:-

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, 7, LENGTH(COLUMN_TYPE) - 8), "','", 1 + units.i + tens.i * 10) , "','", -1)
FROM INFORMATION_SCHEMA.COLUMNS
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
WHERE TABLE_NAME = 'mytable' 
AND COLUMN_NAME = 'mycolumn'

This will work for enums with up to 100 possible values

How can I get All possible values of enum type in mysql through Hibernate

I believe there will be a better way but try this.

in mysql to get description of a col you can use:

desc tableName colName

e.g desc test name

so to get this row in Hibernate:

final Session session = sessionFactory.openSession();
SQLQuery q = session.createSQLQuery("desc tableName colName");
List<Object[]> descRow = q.list().get(0);

//then iterate over the entities

What is the best way to return enum values in MySQL?

I guess you can't select those values out, I ended up parsing the values out with this:

$result = str_replace(array("enum('", "')", "''"), array('', '', "'"), $result);
$arr = explode("','", $result);
return $arr;

MySQL query to get enum values to write them into the dropdown

Your $result object is not the result you expect but the PDO statement to fetch result from.

try something like this after your call to $db->prepare:

//perform SQL query on DB , since it has only be prepared
$result->execute();
//retrieve results from execution, here you obviously expect to get only one result
$data=$result->fetch();

$coltype=$data[0];

Then you'll find the string you want to process (with "enum('xxx','yyy')" in the $coltype variable)

How to get enum field value by its index in mysql

Thank you all for your answers and comments. You've helped me.

Here what I've managed to do. It works in my case.

SELECT 
    AVG(val) average_value, 
    ROUND(AVG(val)) closest_value_id, 
    JSON_UNQUOTE(JSON_EXTRACT(val_enum.items, CONCAT("$[", ROUND(AVG(val)) - 1, "]"))) enum_value
FROM test
CROSS JOIN (
    SELECT CAST(CONCAT('[', REPLACE(SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6), "'", '"'), ']') AS JSON) items
    FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'test' AND COLUMN_NAME = 'val'
) val_enum;

displaying mysql enum values in php

If you have a enum field, the MySQL will return string values for it and you can set with integer and string values too. Simply doing this:

mysql_query("select name from tablename");

will give you full labels like small or medium or large

And you can similarly update them too using full labels like this:

mysql_query("insert into tablename (name) values ('small')");

or numeric values like this:

mysql_query("update tablename set name = 2");

---

Related Topics