MySQL 8.0 – Handling Unexpected Additional Columns

MySQLmysql-8.0

in most of my tables, command

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

worked as expected, but in one table i am getting additional tables that i was not expecting. What are those and how can i not see them ?

enter image description here

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)

As you can see, I am getting 3 additional columns "USER, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS" which i was not expecting. I need just column names as they are so that i can pass those columns to python for parsing

Is maybe name 'users' clashing with some internal Mysql column or other database?

Best Answer

Like @ypercubeᵀᴹ noticed and suggested:

SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = 'mydatabase' 
  AND table_name='users';

does the trick