blocks|key|1488823|text|下面的查询返回有关每个字段的信息列表，包括MySQL字段类型。下面是一个示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1488824|SHOW+FIELDS+FROM+tablename
/*+returns+"Field",+"Type",+"Null",+"Key",+"Default",+"Extras"+*/|code-block|syntax|javascript|1488825|参见this+manual+page。|offset|length|1488826|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/show-columns.html^0|0|0|2|G|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

The query below returns a list of information about each field, including the MySQL field type. Here is an example:

<pre><code>SHOW FIELDS FROM tablename
/* returns "Field", "Type", "Null", "Key", "Default", "Extras" */
</code></pre>

See <a href="http://dev.mysql.com/doc/refman/5.0/en/show-columns.html" rel="noreferrer">this manual page</a>.

blocks|key|1488881|text|您可以使用information_schema+columns表：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1488882|SELECT+DATA_TYPE+FROM+INFORMATION_SCHEMA.COLUMNS+
++WHERE+table_name+=+'tbl_name'+AND+COLUMN_NAME+=+'col_name';|code-block|syntax|javascript|1488883|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/columns-table.html^0|5|Q|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

You can use the <a href="http://dev.mysql.com/doc/refman/5.0/en/columns-table.html" rel="noreferrer">information_schema columns</a> table:

<pre><code>SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE table_name = 'tbl_name' AND COLUMN_NAME = 'col_name';
</code></pre>

blocks|key|1274242|text|获取所有列的数据类型：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1274243|describe+table_name|code-block|syntax|javascript|1274244|或者只有一列：|1274245|describe+table_name+column_name|1274246|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

To get data types of all columns:

<pre><code>describe table_name
</code></pre>

or just a single column:

<pre><code>describe table_name column_name
</code></pre>

blocks|key|1488947|text|请参阅此link|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1488948|mysql>+SHOW+COLUMNS+FROM+mytable+FROM+mydb;
mysql>+SHOW+COLUMNS+FROM+mydb.mytable;|code-block|syntax|javascript|1488949|希望这能对你有所帮助|1488950|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/show-columns.html^0|4|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

Refer this <a href="http://dev.mysql.com/doc/refman/5.0/en/show-columns.html" rel="noreferrer">link</a>

<pre><code>mysql&gt; SHOW COLUMNS FROM mytable FROM mydb;
mysql&gt; SHOW COLUMNS FROM mydb.mytable;
</code></pre>

Hope this may help you

blocks|key|1274311|text|用于找出任何数据库中正在使用的列的所有数据类型的查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1274312|SELECT+distinct+DATA_TYPE+FROM+INFORMATION_SCHEMA.columns+
WHERE+table_schema+=+'<db_name>'+AND+column_name+like+'%25';|code-block|syntax|javascript|1274313|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Query to find out all the datatype of columns being used in any database 

<pre><code>SELECT distinct DATA_TYPE FROM INFORMATION_SCHEMA.columns 
WHERE table_schema = '&lt;db_name&gt;' AND column_name like '%';
</code></pre>

blocks|key|1489091|text|显示mytable中的列|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1489092|自包含的完整示例通常很有用。|1489093|<?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+"$bug";+}

++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+...
++*/
?>|code-block|syntax|javascript|1489094|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

SHOW COLUMNS FROM mytable

Self contained complete examples are often useful.

<pre><code>&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;
</code></pre>

blocks|key|1489112|text|ResultSet+rs+=+Sstatement.executeQuery("SELECT+*+FROM+Table+Name");

ResultSetMetaData+rsMetaData+=+rs.getMetaData();

int+numberOfColumns+=+rsMetaData.getColumnCount();
System.out.println("resultSet+MetaData+column+Count="+%2B+numberOfColumns);

for+(int+i+=+1;+i+<=+numberOfColumns;+i%2B%2B)+{
+System.out.println("column+number+"+%2B+i);

++System.out.println(rsMetaData.getColumnTypeName(i));
}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1489113|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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));
}
</code></pre>

blocks|key|1489182|text|大多数答案都是重复的，对它们进行分组可能会很有用。基本上已经提出了两个简单的选择。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1489183|第一个选项|1489184|第一个选项有4个不同的别名，其中一些别名相当简短：|1489185|EXPLAIN+db_name.table_name;
DESCRIBE+db_name.table_name;
SHOW+FIELDS+FROM+db_name.table_name;
SHOW+COLUMNS+FROM+db_name.table_name;|code-block|syntax|javascript|1489186|(注:作为db_name.table_name的替代方案，可以使用第二个FROM：db_name+FROM+table_name)。|offset|length|style|CODE|1489187|这就给出了一些类似的结果：|1489188|%2B------------------%2B--------------%2B------%2B-----%2B---------%2B-------%2B
%7C+Field++++++++++++%7C+Type+++++++++%7C+Null+%7C+Key+%7C+Default+%7C+Extra+%7C
%2B------------------%2B--------------%2B------%2B-----%2B---------%2B-------%2B
%7C+product_id+++++++%7C+int(11)++++++%7C+NO+++%7C+PRI+%7C+NULL++++%7C+++++++%7C
%7C+name+++++++++++++%7C+varchar(255)+%7C+NO+++%7C+MUL+%7C+NULL++++%7C+++++++%7C
%7C+description++++++%7C+text+++++++++%7C+NO+++%7C+++++%7C+NULL++++%7C+++++++%7C
%7C+meta_title+++++++%7C+varchar(255)+%7C+NO+++%7C+++++%7C+NULL++++%7C+++++++%7C
%2B------------------%2B--------------%2B------%2B-----%2B---------%2B-------%2B|1489189|第二个选项|1489190|第二个选项稍微长了一点：|1489191|SELECT
++COLUMN_NAME,+DATA_TYPE+
FROM
++INFORMATION_SCHEMA.COLUMNS+
WHERE
++TABLE_SCHEMA+=+'db_name'
AND
++TABLE_NAME+=+'table_name';|1489192|它也不那么健谈：|1489193|%2B------------------%2B-----------%2B
%7C+column_name++++++%7C+DATA_TYPE+%7C
%2B------------------%2B-----------%2B
%7C+product_id+++++++%7C+int+++++++%7C
%7C+name+++++++++++++%7C+varchar+++%7C
%7C+description++++++%7C+text++++++%7C
%7C+meta_title+++++++%7C+varchar+++%7C
%2B------------------%2B-----------%2B|1489194|不过，它的优点是允许使用AND+COLUMN_NAME+=+'column_name'+(或like)按列进行选择。|1489195|entityMap^0|0|0|0|0|5|I|10|4|15|N|0|0|0|0|0|0|0|0|C|V|1A|4|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|19|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1A|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|1B|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1C|8|@$M|1D|N|1E|O|P]|$M|1F|N|1G|O|P]|$M|1H|N|1I|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1J|8|@]|9|@]|A|$]]|$1|S|3|T|5|H|7|1K|8|@]|9|@]|A|$I|J]]|$1|U|3|V|5|6|7|1L|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1M|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|H|7|1N|8|@]|9|@]|A|$I|J]]|$1|10|3|11|5|6|7|1O|8|@]|9|@]|A|$]]|$1|12|3|13|5|H|7|1P|8|@]|9|@]|A|$I|J]]|$1|14|3|15|5|6|7|1Q|8|@$M|1R|N|1S|O|P]|$M|1T|N|1U|O|P]]|9|@]|A|$]]|$1|16|3|-4|5|6|7|1V|8|@]|9|@]|A|$]]]|17|$]]

blocks|key|1274513|text|请使用下面的mysql查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1274514|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>'+|code-block|syntax|javascript|1274515|​|1274516|📷|atomic|offset|length|1274517|1274518|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/df26c2ccf6b6a0c036091accdef1da26.jpeg|imageAlt^0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Z|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|10|8|@]|9|@$L|11|M|12|1|13]]|A|$]]|$1|N|3|H|5|6|7|14|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V|W|-4]]]]

Please use the below mysql query.

<pre><code>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;' 
</code></pre>

<a href="https://i.stack.imgur.com/elSjh.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/elSjh.jpg" alt="MySql Query Result"></a>

blocks|key|1274579|text|SELECT+*+FROM+INFORMATION_SCHEMA.COLUMNS+WHERE+TABLE_SCHEMA='SCHEMA_NAME‘AND+COLUMN_KEY='PRI'；WHERE+COLUMN_KEY='PRI'；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1274580|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='SCHEMA_NAME' AND COLUMN_KEY='PRI';
 WHERE COLUMN_KEY='PRI';

blocks|key|1274589|text|SELECT+
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,+
COLUMN_NAME,+
DATA_TYPE+
FROM+INFORMATION_SCHEMA.COLUMNS+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1274590|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT 
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME, 
COLUMN_NAME, 
DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
</code></pre>

I want to get the column data type of a mysql table.

Thought I could use <code>MYSQLFIELD</code> structure but it was enumerated field types.

Then I tried with <code>mysql_real_query()</code>

The error which i am getting is <code>query was empty</code>

How do I get the column data type?

How to get the mysql table columns data type?

数据库

MySQL

我想要获取mysql表的列数据类型。我认为我可以使用MYSQLFIELD结构，但它是枚举字段类型。然后我试着用mysql_real_query()我得到的错误是query was empty如何获取列数据类型？

问如何获取mysql表列的数据类型？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取mysql表列的数据类型？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取mysql表列的数据类型？
EN