How to get the mysql table columns data type

Question

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

User · Answer

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

User · Answer

Please use the below mysql query   SELECT COLUMN NAME  DATA TYPE  CHARACTER MAXIMUM LENGTH  FROM information schema columns  WHERE table schema     lt DATABASE NAME gt    AND table name     lt TABLE NAME gt    AND COLUMN NAME     lt COLOMN NAME gt

User · Answer

To get data types of all columns   describe table name   or just a single column   describe table name column name

User · Answer

Refer this link  mysql gt  SHOW COLUMNS FROM mytable FROM mydb  mysql gt  SHOW COLUMNS FROM mydb mytable    Hope this may help you

User · Answer

SHOW COLUMNS FROM mytable  Self contained complete examples are often useful    lt  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   gt   SET NAMES utf8     try          pdo   new PDO  mysql host localhost dbname mydatabase    databaseuser    thesecretpassword   array PDO  MYSQL ATTR INIT COMMAND   gt   SET NAMES utf8               catch PDOException  e          die  Could not connect       e- gt getMessage             sql      SHOW COLUMNS FROM mytable      query    pdo- gt prepare  sql      query- gt execute        err    query- gt errorInfo       bug    err 2      if   bug          echo   lt p gt  bug lt  p gt         while   row    query- gt fetch PDO  FETCH ASSOC           echo   lt pre gt     print r  row  true      lt  pre gt              OUTPUT SAMPLE     Array            Field    gt  page id        Type    gt  char 40         Null    gt  NO        Key    gt          Default    gt          Extra    gt          Array              Field    gt  last name        Type    gt  char 50        More            gt

User · Answer

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

User · Answer

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

User · Answer

SELECT    FROM  INFORMATION SCHEMA COLUMNS  WHERE TABLE SCHEMA  SCHEMA NAME  AND COLUMN KEY  PRI     WHERE COLUMN KEY  PRI

User · Answer

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  lt   numberOfColumns  i       System out println  column number     i      System out println rsMetaData getColumnTypeName i

User · Answer

First select the Database using use testDB  then execute  desc  testDB   images   -- or SHOW FIELDS FROM images    Output

User · Answer

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     lt db name gt   AND column name like

[mysql] How to get the mysql table columns data type?

Examples related to mysql