How to Get Column Information from MySQL Command Line

command lineMySQLscripting

I am trying to get all the columns from MySQL using bash scripting. I want them to be stored in an array. If I write it this way:

mysql -uroot -pPassword1 "select column_name 
    from information_schema.columns 
    where table_schema = 'dbName' 
    and table_name = 'tableName';"

…I get an error that the identifier name is too long. If I write this way:

mysql -uroot -pPassword1 <<- SMTH
    select column_name 
    from information_schema.columns 
    where table_schema = 'dbName' 
    and table_name = 'tableName';
SMTH

everything works fine. But I can't output it to a variable. Any suggestions?

Best Answer

Add -e option:

· --execute=statement, -e statement

Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.2.4, “Using Options on the Command Line”, for some examples. With this option, mysql does not use the history file.

mysql -uroot -pPassword1 -e "select column_name from information_schema.columns where table_schema = 'dbName' and table_name = 'tableName';"

Test:

root@onare:/home/onare# mysql -uroot -ponaare -e "select column_name from information_schema.columns where table_schema = 'test' LIMIT 10;"
+--------------+
| column_name  |
+--------------+
| AnswerID     |
| QuestionID   |
| ReplyContent |
| id           |
| emp_id       |
| call_start   |
| call_end     |
| call_type    |
| idEmployee   |
| eName        |
+--------------+
root@onare:/home/onare#

EDIT 2:

If you want to avoid the query header (column_name), use --skip-column-name:

root@onare:/home/onare# mysql -uroot -ponaare -e "select column_name from information_schema.columns where table_schema = 'test' LIMIT 10;" --skip-column-names
+--------------+
|     AnswerID |
|   QuestionID |
| ReplyContent |
|           id |
|       emp_id |
|   call_start |
|     call_end |
|    call_type |
|   idEmployee |
|        eName |
+--------------+
root@onare:/home/onare#
Related Question