Regular Expression to parse SQL Structure

Question

I am trying to parse the MySQL data types returned by "DESCRIBE [TABLE]".

It returns strings like:

int(11)
float
varchar(200)
int(11) unsigned
float(6,2)

I've tried to do the job using regular expressions but it's not working.

PHP CODE:

$string = "int(11) numeric";<br/>
$regex = '/(\w+)\s*(\w+)/';<br/>
var_dump( preg_split($regex, $string) );<br/>

Answer 1

Why not just pull meta-data from the fields directly?

...
$meta = mysql_fetch_field($result, $i);
echo $meta->type;
echo $meta->max_length;

Answer 2

Query the information schema for introspection of tables and columns, and more.

Answer 3

I wrote code to do this in Zend_Db_Adapter_Mysqli::describeTable().
It handles varchar, char, decimal, float, and all MySQL integer types.

I won't post the code here because of StackOverflow's CC-wiki license policy, but go check it out at that link.

mysql_fetch_field() only reports data types for query result sets, not persisted tables.

INFORMATION_SCHEMA is another option, but MySQL's early implementation of it has incredibly poor performance, which makes it hard to use in a web app that needs quick response time. Some changes in MySQL 5.1.23 tried to improve the performance, but some people still report problems.