Regular expression for SQL structure analysis

Question

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

It returns strings like:

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

I tried to complete the task using regular expressions, but it does not work.

PHP CODE:

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

Answer 1

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

I will not post the code here due to the StackOverflow CC-wiki license policy, but follow this link.

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

INFORMATION_SCHEMA is another option, but its early implementation in MySQL has incredibly low performance, which makes it difficult to use in a web application that requires fast response times. Some changes in MySQL 5.1.23 tried to improve performance, but some people still report problems.

Answer 2

Request an information schema for introspecting tables and columns, etc.

Answer 3

Why not just pull the metadata from the fields directly ?

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