如何获得 mysql 表列的数据类型？

下面的查询返回关于每个字段的信息列表，包括 MySQL 字段类型:

SHOW FIELDS FROM tablename
/* returns "Field", "Type", "Null", "Key", "Default", "Extras" */

参见 本手册。

您可以使用 Information _ schema 列表:

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name' AND COLUMN_NAME = 'col_name';

要获取所有列的数据类型:

describe table_name

或者只是一个专栏:

describe table_name column_name

参考此 链接

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

希望这个能帮到你

首先使用 use testDB;选择数据库，然后执行

desc `testDB`.`images`;
-- or
SHOW FIELDS FROM images;

产出:

查询来查找任何数据库中使用的所有列的数据类型

SELECT distinct DATA_TYPE FROM INFORMATION_SCHEMA.columns
WHERE table_schema = '<db_name>' AND column_name like '%';

显示 mytable 中的列

自包含的完整示例通常很有用。

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

大多数答案都是重复的，将它们分组可能会有用。

第一个选择

第一个选项有4个不同的别名，其中一些很短:

EXPLAIN db_name.table_name;
DESCRIBE db_name.table_name;
SHOW FIELDS FROM db_name.table_name;
SHOW COLUMNS FROM db_name.table_name;

注意: 在每种情况下，你也可以写两次 FROM而不是 db_name.table_name，例如:

SHOW FIELDS FROM table_name FROM db_name

这就像是:

+------------------+--------------+------+-----+---------+-------+
| 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    |       |
+------------------+--------------+------+-----+---------+-------+

第二个选择

第二个选择稍微长一点:

SELECT
COLUMN_NAME, DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = 'db_name'
AND
TABLE_NAME = 'table_name';

它也不那么健谈:

+------------------+-----------+
| column_name      | DATA_TYPE |
+------------------+-----------+
| product_id       | int       |
| name             | varchar   |
| description      | text      |
| meta_title       | varchar   |
+------------------+-----------+

它的优点是允许使用 AND COLUMN_NAME = 'column_name'(或 like)对每列进行选择。

请使用下面的 mysql 查询。

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>'

选择 * FROM INFORMATION _ SCHEMA. COLUMN WHERE TABLE _ SCHEMA = ‘ SCHEMA _ Name’AND COLUMN _ KEY = ‘ PRI’; 其中列 _ KEY = ‘ PRI’;

SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS