CODEWITHSUNDEEP
mysqlfieldtype
Matt Bannert
Matt Bannert

Reputation: 28264

How to return field type from MySQL query?

Simple question: How can I return the field type of a MySQL table. I know about describe or show column but I just want to return that single parameter. e.g.:

SELECT fieldtype(mycol) FROM mytable
# should return INT or integer for example

Upvotes: 19

Views: 59910

Answers (5)

SRK
SRK

Reputation: 128

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));
}

Upvotes: 0

spelunk1
spelunk1

Reputation: 787

The following syntax also works, using describe, enter the columnName after the tableName:

describe tbleName columnName;

Upvotes: 2

Jules
Jules

Reputation: 2021

I know I'm a little late to the party. You may want to use COLUMN_TYPE rather than DATA_TYPE. It gives more info (such as precision) about the type.

SELECT COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'parts'
AND COLUMN_NAME = 'price'

...yields...

decimal(11,2)

Upvotes: 12

Gaurav
Gaurav

Reputation: 28755

You can use 

SHOW FIELDS
FROM tableName where Field ='nameOfField'

This will return you result in format of

Field   Type    Null    Key     Default     Extra

Upvotes: 26

Ike Walker
Ike Walker

Reputation: 65537

You can get this from the information_schema database:

select data_type 
from information_schema.columns 
where table_schema = 'myschema'
and table_name = 'mytable' 
and column_name = 'mycol'

Upvotes: 18

Related Questions