Querying and parsing MySQL data types

LanX has asked for the wisdom of the Perl Monks concerning the following question:

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",
[download]

but informations like unsigned and zerofill seem to get lost.

Cheers Rolf
_{(addicted to the Perl Programming Language :)

Wikisyntax for the Monastery
FootballPerl is like chess, only without the dice}

update

fixed format issue, thanks stevieb++

Comment on Querying and parsing MySQL data types Select or Download Code

Replies are listed 'Best First'.

Re: Querying and parsing MySQL data types
by kschwab (Vicar) on Nov 30, 2018 at 22:19 UTC

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 |
+------------+------------------+------+-----+---------+----------------+

[reply]

Re^2: Querying and parsing MySQL data types

by LanX (Saint) on Nov 30, 2018 at 22:35 UTC

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

Cheers Rolf
_{(addicted to the Perl Programming Language :)

Wikisyntax for the Monastery
FootballPerl is like chess, only without the dice}

[reply]
[d/l]