Mysql – setting in MySQL that will allow you to see the columns of a table on a query, even if the table is empty

configurationdatabase-designMySQL

When I am writing code to go with a certain MySQL query, I like to do a basic select statement to see the columns, and to perhaps see what data is available as a sample. Sometimes, the table I would like to look at has no data, so I get a completely empty query.

Now, I know I can use something like "show create table [table_name]" to see the query; similarly, I am aware that it's possible to show the tables with a join statement, but the syntax is just complex enough that I don't always remember it, and I don't want to add complexity to every simply query I want to make, just to handle the occasional empty table.

Thus, I was wondering: is there a setting in MySQL's client, that would allow me to see the column names every time, even when the table is empty?

Best Answer

This will always return a single row with NULL in all the columns of the table:

SELECT t.* 
FROM (SELECT 1) AS dummy 
  LEFT JOIN table_name AS t
    ON FALSE ;

This will always return a single row with NULL in all the columns of the table if the table is empty and real data from one row if it isn't empty:

SELECT t.* 
FROM (SELECT 1) AS dummy 
  LEFT JOIN table_name AS t
    ON TRUE
LIMIT 1 ;

Related Solutions

Mysql – “show create table” output with out “show”

If you want something that's quick-and-dirty, may you should consider doing this:

STEP 01) Use mysqldump

mysqldump --no-data --all-databases > MySQLSchema.sql

STEP 02) Parse the text

Every CREATE TABLE would mark the next databse

Every USE dbname marks the name of the next database

Have each CREATE TABLE description in a text file

STEP 03) Do a LOAD DATA INFILE of each of the parsed files into a table of your choice (such as mydb.mytables)

STEP 04) Query for the structures

SELECT table_desc FROM mydb.mytables WHERE dbname="...' AND tbname='...';

Your your would look something like this:

CREATE TABLE mytables
(
    dbname VARCHAR(64) NOT NULL,
    tbname VARCHAR(64) NOT NULL,
    table_desc TEXT DEFAULT NULL,
    primary key (dbname,tbname)
);

Your mission is to parse the mysqldump I just mentioned and get each table description loaded into this table, along with the database anem table name.

Once you construct such a parsing program, you could do this process every hour and update your dynamic documentation.

Give it a Try !!!

UPDATE 2012-04-01 00:39 EDT

Here is modified suggestion: Given the mysqldump suggestion I made, your should load tMySQL Schema attained from the mysqldump into another DB server that contains no data. Set it up as a replication slave using replicate-do-db=mysql. That way, no data will collect in the slave. You can use this slave as the source of the mysqldumping of the DB schema. This separates the data from the schema. You can use your documentation system fetch the schema from the slave.

Mysql – InnoDB query duration inconsistent

ASPECT #1 : InnoDB

What immediately catches my eye is the fact that the table has no explicit PRIMARY KEY. This means that an internal rowID Clustered Index (gen_clust_index) is being used.

With each UPDATE command, you may encounter moments when the row is actually locked in conjunction with the Clustered Index. Consequently, the data page gets locked.

Here is a question: Is it possible for the PRIMARY KEY (or rowID) of two different rows to deadlock? Surprisingly, the answer is Yes. Rather than explain why, I will refer you to three questions asked by one person: RedBlueThing.

I managed to answer his three questions about deadlocking pages:

Jun 14, 2011 : Reasons for occasionally slow queries?
Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?
Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

It is possible that two or more rows occupy the same data page. In fact, the rowID-based PRIMARY KEY coexists with the data on the same data page. While crisscrossing rows, perhaps some miscellaneous garbage collection (data structures within the InnoDB Buffer Pool that are released and reallocated) may be happening.

ASPECT #2 : Your C# Code

Inside the loop, you are also doing garbage collection and reallocation for the DB Connector. That needs to be reorganized, perhaps placing it outside the loop.

SUGGESTION

Try setting up the test using the transaction paradigm with BeginExecuteNonQuery()

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.BeginExecuteNonQuery();
for (int i = 0; i < 15; i++) {
   long start = CurrentTimeMillis();

   cmd.CommandText = "UPDATE test SET col1=" + i + " WHERE col2=1";
   cmd.Prepare();
   cmd.ExecuteNonQuery();

   Console.WriteLine("Duration: " + (CurrentTimeMillis() - start));
}
cmd.EndExecuteNonQuery();