How to get the mysql table columns data type?
I want to get the column data type of a mysql table.
Thought I could use MYSQLFIELD
structure but it was enumerated field types.
Then I tried with mysql_real_query()
The error which i am getting is query was empty
How do I get the column data type?
You can use the information_schema columns table:
SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name' AND COLUMN_NAME = 'col_name';
The query below returns a list of information about each field, including the MySQL field type. Here is an example:
SHOW FIELDS FROM tablename
/* returns "Field", "Type", "Null", "Key", "Default", "Extras" */
See this manual page.
Most answers are duplicates, it might be useful to group them. Basically two simple options have been proposed.
First option
The first option has 4 different aliases, some of which are quite short :
EXPLAIN db_name.table_name;
DESCRIBE db_name.table_name;
SHOW FIELDS FROM db_name.table_name;
SHOW COLUMNS FROM db_name.table_name;
(NB : as an alternative to db_name.table_name
, one can use a second FROM
: db_name FROM table_name
).
This gives something like :
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| product_id | int(11) | NO | PRI | NULL | |
| name | varchar(255) | NO | MUL | NULL | |
| description | text | NO | | NULL | |
| meta_title | varchar(255) | NO | | NULL | |
+------------------+--------------+------+-----+---------+-------+
Second option
The second option is a bit longer :
SELECT
COLUMN_NAME, DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = 'db_name'
AND
TABLE_NAME = 'table_name';
It is also less talkative :
+------------------+-----------+
| column_name | DATA_TYPE |
+------------------+-----------+
| product_id | int |
| name | varchar |
| description | text |
| meta_title | varchar |
+------------------+-----------+
It has the advantage of allowing selection per column, though, using AND COLUMN_NAME = 'column_name'
(or like
).