OneFreeVoice

Back when I was doing a lot of work with Oracle, I learned to lean heavily on the SYSTEM views - Oracle’s equivalent to the INFORMATION_SCHEMA database. These views can really help you when it comes to writing dynamic SQL in stored procedures, or just taking quick shortcuts while you’re writing code. Or, if you’re like me, using the information schema can really help you limit the number of types you make.

Let’s take a simple case. You want to insert a row into a table that has multiple columns. You can hand type all those columns, but for me, that’s takes time and by the time I’m done debugging all the typos, it takes a really long time. Instead I just fire up a separate mysql client in silent mode and run a simple query:

select concat(column_name,', ')
  from information_schema columns
 where table_schema = "myschema"
   and table_name = "mytable";

So for a simple example with the mysql.user table:

select concat(column_name,', ')
  from information_schema columns
 where table_schema = "mysql"
   and table_name = "user";
Host,
User,
Password,
Select_priv,
Insert_priv,
Update_priv,
Delete_priv,
Create_priv,
Drop_priv,
Reload_priv,
Shutdown_priv,
Process_priv,
File_priv,
Grant_priv,
References_priv,
Index_priv,
Alter_priv,
Show_db_priv,
Super_priv,
Create_tmp_table_priv,
Lock_tables_priv,
Execute_priv,
Repl_slave_priv,
Repl_client_priv,
Create_view_priv,
Show_view_priv,
Create_routine_priv,
Alter_routine_priv,
Create_user_priv,
ssl_type,
ssl_cipher,
x509_issuer,
x509_subject,
max_questions,
max_updates,
max_connections,
max_user_connections,

So I just copy this directly out of the client and paste it into my script - the only editing I have to do is remove the very last comma.

Now, you may note that my output above is doesn’t have the formatting that you typically see from your mysql client . The typical output looks more like:

+--------------------------+
| concat(column_name,', ') |
+--------------------------+
| Host,                    |

You can get rid of the extra characters and spaces by using the silent mode switch (-s) when you run the mysql client.*

Let’s look at another quick practical example - you want to take a dump of your data for testing, but you don’t want archive data. One way or another, you’re probably going to have to run a query to find your archive tables, so you might as well take that a step farther and get an output you can reuse:

SELECT CONCAT('--ignore-table=',
              table_schema,
              '.',
              table_name,
              ' \\')
  FROM information_schema.tables
 WHERE engine='ARCHIVE'
   AND table_schema='myschema';

--ignore-table=myschema.archive_table1 \
--ignore-table=myschema.archive_table2 \
--ignore-table=myschema.archive_table3 \
--ignore-table=myschema.archive_table4 \

I just copy the above into a BASH script and I’m ready to go.

I have seen situations where people take this much farther and use the schema to dynamically create SQL inside of stored procedures. I chose not to post an example here. While I will admit there are some situations where it is useful, maybe even necessary to do so, I don’t much like dynamic SQL as it runs a little too close to soft coding.

* Note: at the time of this post, there is a bug in mySQL that causes special characters to be output twice. In the case of my last example, you actually have to trim the last backslash off of each line before