Mysql command line display examples

Posted on 2016-07-14
Medium Priority
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 1000 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 500 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 51

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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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 51

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 51

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 51

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 51

Assisted Solution

by:Steve Bink
Steve Bink earned 500 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

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
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…
Suggested Courses

752 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