tmdag's questions

在我的大多数表中，命令

SELECT column_name FROM information_schema.columns WHERE table_name='table'

按预期工作，但在一张表中，我得到了我没想到的其他表。那些是什么，我怎么看不到它们？

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+

mysql> DESCRIBE users;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| userID      | int(11)      | NO   | PRI | NULL    | auto_increment |
| username    | varchar(20)  | YES  |     | NULL    |                |
| password    | varchar(100) | YES  |     | NULL    |                |
| email       | varchar(50)  | YES  |     | NULL    |                |
| avatar      | varchar(255) | YES  |     | NULL    |                |
| admin       | tinyint(1)   | YES  |     | NULL    |                |
| createdTime | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

mysql> SELECT column_name FROM information_schema.columns WHERE table_name='users' ORDER BY ORDINAL_POSITION;
+---------------------+
| COLUMN_NAME         |
+---------------------+
| userID              |
| USER                |
| CURRENT_CONNECTIONS |
| username            |
| password            |
| TOTAL_CONNECTIONS   |
| email               |
| avatar              |
| admin               |
| createdTime         |
+---------------------+
10 rows in set (0.01 sec)

如您所见，我得到了 3 个额外的列“USER、CURRENT_CONNECTIONS、TOTAL_CONNECTIONS”，这是我没有预料到的。我只需要列名，这样我就可以将这些列传递给 python 进行解析

名称“用户”是否可能与某些内部 Mysql 列或其他数据库发生冲突？

我的印象是列的默认顺序是内部 mysql 顺序，但出于某种原因描述表并选择 column_name 给了我完全不同的顺序。

如何将 ORDER BY 设置为与“描述表”（“默认”表顺序 - 不是按名称）相同？

Server version: 8.0.12 MySQL Community Server - GPL
mysql> describe assetType;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| assetTypeID | int(11)     | NO   | PRI | NULL    | auto_increment |
| assetName   | varchar(45) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT column_name FROM information_schema.columns WHERE table_name='assetType';
+-------------+
| COLUMN_NAME |
+-------------+
| assetName   |
| assetTypeID |
+-------------+
2 rows in set (0.00 sec)