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?
112 Answers
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.
3Most 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: In each case, you can also write FROM two times instead of db_name.table_name, example:
SHOW FIELDS FROM table_name FROM db_nameThis 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).
To get data types of all columns:
describe table_nameor just a single column:
describe table_name column_namePlease use the below mysql query.
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE table_schema = '<DATABASE NAME>'
AND table_name = '<TABLE NAME>'
AND COLUMN_NAME = '<COLOMN NAME>' First select the Database using use testDB; then execute
desc `testDB`.`images`;
-- or
SHOW FIELDS FROM images;Output:
Refer this link
mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;Hope this may help you
Query to find out all the datatype of columns being used in any database
SELECT distinct DATA_TYPE FROM INFORMATION_SCHEMA.columns
WHERE table_schema = '<db_name>' AND column_name like '%';SHOW COLUMNS FROM mytable
Self contained complete examples are often useful.
<?php // The server where your database is hosted localhost // The name of your database mydatabase // The user name of the database user databaseuser // The password of the database user thesecretpassword // Most web pages are in utf-8 so should be the database array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") try { $pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "databaseuser", "thesecretpassword", array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")); } catch(PDOException $e) { die('Could not connect: ' . $e->getMessage()); } $sql = "SHOW COLUMNS FROM mytable"; $query = $pdo->prepare($sql); $query->execute(); $err = $query->errorInfo(); $bug = $err[2]; if ($bug != "") { echo "<p>$bug</p>"; } while ($row = $query->fetch(PDO::FETCH_ASSOC)) { echo "<pre>" . print_r($row, true) . "</pre>"; } /* OUTPUT SAMPLE Array ( [Field] => page_id [Type] => char(40) [Null] => NO [Key] => [Default] => [Extra] => ) Array ( [Field] => last_name [Type] => char(50) More ... */
?>ResultSet rs = Sstatement.executeQuery("SELECT * FROM Table Name");
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
System.out.println("resultSet MetaData column Count=" + numberOfColumns);
for (int i = 1; i <= numberOfColumns; i++) { System.out.println("column number " + i); System.out.println(rsMetaData.getColumnTypeName(i));
}SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='SCHEMA_NAME' AND COLUMN_KEY='PRI'; WHERE COLUMN_KEY='PRI';
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
