MySQL 如何获取mysql表列数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1215368/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the mysql table columns data type?
提问by krishnakumar
I want to get the column data type of a mysql table.
我想获取 mysql 表的列数据类型。
Thought I could use MYSQLFIELDstructure but it was enumerated field types.
以为我可以使用MYSQLFIELD结构，但它是枚举字段类型。
Then I tried with mysql_real_query()
然后我试过 mysql_real_query()
The error which i am getting is query was empty
我得到的错误是 query was empty
How do I get the column data type?
如何获取列数据类型？
回答by brian-brazil
You can use the information_schema columnstable:
您可以使用information_schema列表：
SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name' AND COLUMN_NAME = 'col_name';
回答by James Skidmore
The query below returns a list of information about each field, including the MySQL field type. Here is an example:
下面的查询返回有关每个字段的信息列表，包括 MySQL 字段类型。下面是一个例子：
SHOW FIELDS FROM tablename
/* returns "Field", "Type", "Null", "Key", "Default", "Extras" */
See this manual page.
请参阅此手册页。
回答by Skippy le Grand Gourou
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?:
第一个选项有 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;
(NB?: as an alternative to db_name.table_name, one can use a second FROM?: db_name FROM table_name).
（注意？：作为 的替代db_name.table_name，可以使用第二个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).
但是，它的优点是允许每列选择，但使用AND COLUMN_NAME = 'column_name'(或like)。
回答by jondinham
To get data types of all columns:
获取所有列的数据类型：
describe table_name
or just a single column:
或者只是一列：
describe table_name column_name
回答by Prasant Kumar
Please use the below mysql query.
请使用以下 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>'
回答by sonu thomas
回答by UdayKiran Pulipati
First select the Database using use testDB;then execute
首先选择数据库使用use testDB;然后执行
desc `testDB`.`images`;
-- or
SHOW FIELDS FROM images;
Output:
输出：
回答by user3419013
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 '%';
回答by nbauers
SHOW COLUMNS FROM mytable
显示来自 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 ...
*/
?>
回答by SRK
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));
}
