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
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Suggested Courses

621 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