Querying and parsing MySQL data types

[LanX]

Hi

I'm most probably reinventing the wheel

I had to write a parser to parse mysql types like

• smallint(6) unsigned zerofill
• enum('export','import')
• enum('Y','N')

Is there any way to get this type info in a more atomic and standardized way?

I tried DBI::column_info and querying information_schema.`COLUMNS` but couldn't get anything which didn't required writing a parser.

background

My job involves writing many HTML/JS GUIs for our data models and my colleagues are quite "agile" in changing data-models on the fly. Our schemas are replicated (but not synchronized) on different servers.

Now I'm trying to automate type-checking and form generation on the GUI level.

Such that enum('Y','N') results in a [ ] check box and enum('export','import','view') in a select menu.

update

actually using DBI::column_info I'm getting quite detailed informations for enum

    mysql_type_name         => "enum('Y','N')",
    mysql_values            => ["Y", "N"],
    TYPE_NAME               => "ENUM",

but informations like unsigned and zerofill seem to get lost.

update

fixed format issue, thanks stevieb++

[kschwab]

You mentioned "COLUMNS from information_schema", but you can get the same info with a little simpler syntax:

mysql> SHOW COLUMNS FROM student LIKE 's%';
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| sex        | enum('F','M')    | NO   |     | NULL    |                |
| student_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+------------+------------------+------+-----+---------+----------------+

I suppose that doesn't help much, because you still have to parse out all the stuff in 'Type' and 'Extra'.

[LanX]

Thanks, TIMTOWTDI and I tried most of them:

DESCRIBE t_tm1_export_import_attributes;

Field	Type	Null	Key	Default	Extra
f_node_id	int(10) unsigned	NO	PRI
f_transfertype	enum('export','import')	NO		export
f_cube	varchar(100)	NO		cubename
f_active	enum('Y','N')	NO		Y
f_checkposliste	enum('0','1')	NO		0
f_kumulativ	enum('0','1')	NO		0
f_bstdimnr	int(11)	NO		0
f_file	varchar(300)	NO
f_info	varchar(300)	NO