Mysql command line display examples

Posted on 2016-07-14
Last Modified: 2016-07-19
From the Linux (centos in this case) I need to compare some mysql entries and looking from left to right is not an option.
I need to putt the data from long rows, displaying the column name and it's value in the form of a list I can latter compare side to side using another tool.

I need the output formatted so that the first column is the field name and the second columns are the values.
To make things more readable, optionally, the spacing between the two columns should be based on the longest field name I would suppose.

For example

ID                                 34
name                          something
date                            somedate
value1                        somevalue
value2                        somevalue
value3                        somevalue

Anyhow, I'm looking for a bash script to do this as I am not a programmer.

Thanks very much.
Question by:projects
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 250 total points
ID: 41711714
this is not "possible" in a normal sql statment, neither in mysql nor in other dbms languages.
this could be done in some reporting tools more easily (transpose)
I understand your need, and I would suggest that you do this rather in phpmyadmin, for example, which, as far as I remember, has such a record display.
alternatively you could use ms access, for example, create the linked table, and map a form with the requested layout (also available as standard option there) for your table.
LVL 15

Assisted Solution

Insoftservice earned 125 total points
ID: 41711743
This is not rdbms functionality it can be easily done in NOSQL ,Redis and hypertable.
For doing this thing in rdbms you have to create one more column
name as id
ID    column_name column_value
34    Name               something
34    date                  something
34    ---                        ---
LVL 50

Expert Comment

by:Steve Bink
ID: 41711744
Angel is generally correct, but there is a really crappy way to do this - UNION.  Something like this:
SELECT 'id' AS Field_Name, id AS Field_Value FROM MyTable WHERE ID=@
SELECT 'name', name FROM MyTable WHERE ID=@
SELECT 'value1', value1 FROM MyTable WHERE ID=@
SELECT 'value2', value2 FROM MyTable WHERE ID=@
SELECT 'value3', value3 FROM MyTable WHERE ID=@;

Open in new window

The bash script would take your id value as the parameter.  It would do a text replace, switching the '@' with the value, then run the query.  The results would come out pretty similar to what you want.

That said, your formatting requirement are oddly specific for just wanting to get some data.  You might want to consider an actual reporting engine - PHPMyAdmin and MSAccess can both fill the role..  sort of..  Maybe if you explain a little bit more about your end goal, we can recommend a better process for you to follow.
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.


Author Comment

ID: 41713258
It doesn't have to be as specific as I posted but I find it's better to try and be as specific as possible when posting on this site.

I know there is no way to do this using mysql cli or phpmyadmin which is why I asked for a script. Basically, as you (Steve Bink) mention, that is the idea. Read a column, print column name then the value, read another column, print the column name then the value.

I don't want to use another program, I just need a quick and dirty script to do this because we need to compare tables and contents now and then and a side to side comparison is the only way to get it done.
LVL 50

Expert Comment

by:Steve Bink
ID: 41713312
>>> we need to compare tables and contents now and then and a side to side
>>> comparison is the only way to get it done.

What is the need behind the comparison?  What actions are taken based on the comparison results?

Another possibility is to do full-row comparisons using diff, and leverage grep to programmatically identify the differences.

Author Comment

ID: 41713637
The need is simply to compare a large number of tables when trying to debug certain problems. The only way to do it is by looking at each column and its value which is hard to do in any way but a simple text output.

With that output, we can enter it into something like a side by side compare tool and more easily find problems.
LVL 50

Expert Comment

by:Steve Bink
ID: 41713763
I would just use "SELECT *" for whichever IDs you want to compare, then paste them into Excel.  They won't be side-by-side, but they will be visible rows next to each other.  For that matter, a decent client, such as HeidiSQL would let you do the same thing.

BTW, if you have SSH access, but MySQL is not exposed publicly, you can use PuTTY to create an SSH tunnel to allow clients like HeidiSQL to do their work.

Author Comment

ID: 41714626
That's too much work. It's easier to create a script once and have it output what I can paste into a compare tool.
Yes, command line of course but I noticed I forgot a word when writing up my question.

>From the Linux (centos in this case) I need to compare
was supposed to be
From the Linux (centos in this case) cli, I need to compare
LVL 50

Expert Comment

by:Steve Bink
ID: 41714713
If you're unwilling to use another client, and also do not want to use a third-party application to assist, then you're stuck with the UNION strategy I outlined earlier.

Author Comment

ID: 41719812
That doesn't seem to make any sense.
I'm sure I can write a simple script that pulls each columns value into a single line in a file.
The problem is that not being a programmer, I would probably not do it as well as someone who is could.
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 125 total points
ID: 41719885
As Angel commented originally, what you want is not how RDBs are designed to work.  Insoftservice suggested the possibility of using other, non-relational systems - those may work better for this, but you'll also have to contend with porting your data into it.  My suggestion of using "UNION" is as close as you'll get using SQL.  Otherwise, any script you write is going to boil down to "SELECT field1; print it; SELECT field2; print it; etc..", which is just the UNION method done the hard way.

So, short answer, yes, a script is possible.  I just feel that is a lot more work than just looking at the two rows in something like Excel...  using a pair of pliers to turn a screw.

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
Recently, an awarded photographer, Selina De Maeyer (, completed a photo shoot of a beautiful event ( in An…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question