2

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?

4
  • There must be another table called users in another database. Try using SELECT column_name, table_schema ... Sep 3, 2018 at 20:30
  • I think there is one in the performance_schema, so yeah, it is internal. Sep 3, 2018 at 20:32
  • You can add AND table_schema = 'the_name_of_your_database' in the WHERE clause to get only the columns from the table you need. Sep 3, 2018 at 20:35
  • 1
    If you are using a single database, use ypercube/tmdag's answer, else use JayRizzo's.
    – Rick James
    Oct 2, 2018 at 14:46

2 Answers 2

2

Like @ypercubeᵀᴹ noticed and suggested:

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

does the trick

1

This is fairly common to have happen.

I believe you see these because you have permissions (read) on these as well.

as far as

What are those and how can i not see them ?

These are system schemas.

Breakdown

If your not a

  1. DBA
  2. Concerned about performance, security or maintenance
  3. Where your custom snippets live.

They probably will be a waste of your time.

Either way if you do SQL I would urge you to read up on them, as there can be many beneficial items in there.

SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 
                           'information_schema', 
                           '.mysqlworkbench', 
                           'performance_schema');

This works for tables as well.

SELECT table_name
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql',
                           'information_schema',
                           '.mysqlworkbench',
                           'performance_schema');
2
  • 1
    Suggest TABLE_SCHEMA NOT IN ('sys', 'mysql', ...) as more compact.
    – Rick James
    Oct 2, 2018 at 14:45
  • Thanks @RickJames, I cleaned it up. That actually does look better. Cheers!
    – JayRizzo
    Oct 2, 2018 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.