Database Developers' Quick-Reference to MySQL DDL

Some time back I wrote an article to help DB developers get started in MySQL.
Database Developers' Quick-Reference to MySQL

Now I'm adding some Data Definition Language (DDL) statements to that.

1. Describe

What does the silly table look like?  What are it's fields and what are their types?
MS SQL:

exec sp_help tablename

Select all Open in new window

MySQL:

Describe tablename;

Select all Open in new window

2. information_schema tables

information_schema is presented as its own DB.  Actually, I think it is its own Schema, but all the stuff a SQL Server developer would group as a database, MySQL groups as a Schema.

Most of your schema queries are going to one or more of the information_schema tables.
Consider information_schema.tables roughly equivalent to sys.tables and information_schema.columns roughly equivalent to syscolumns.

So ... the next several items will make use of that knowledge.

3. Does Table X Exist in my MySQL DB?

This is as easy as in SQL Server, but a bit different. In MySQL it's:

SELECT COUNT( * ) FROM information_schema.tables WHERE table_schema = 'MyDBName' AND table_name = 'MyTableName'; 

Select all Open in new window

4. Does Table X Have Column Y in my MySQL DB?

This one's actually a bit easier than in MS SQL Server as no join is required ...

SELECT count( * ) FROM information_schema.columns WHERE table_schema = 'MyDBName' AND Table_Name = 'MyTableName' AND column_Name = 'MyColumnName'; 

Select all Open in new window

5. Add table only if it does not exist

This one's better in MySQL than in MS SQL Server also:

CREATE TABLE IF NOT EXISTS tablename ( ... ); 

Select all Open in new window

6. Drop Table only if it Exists

This one's better in MySQL than in MS SQL Server also
Again, there's just an If Exists at the end of the DDL statement.

DROP TABLE IF EXISTS tablename; 

Select all Open in new window

7. Add a column only if it does not exist

This one's clunky! YUCK!!!
Because the IF statement is only available within procedures and functions, you have to have a procedure that does this. The solution is here:
http://www.cryer.co.uk/brian/mysql/howto_add_column_unless_exists.htm

8. Drop a column only if it exists

Clunky just like the above:

delimiter '//' ;
                      create procedure DropColumnIfExists(
                      	IN dbName tinytext,
                      	IN tableName tinytext,
                      	IN fieldName tinytext)
                      begin
                      	IF EXISTS (
                      		SELECT * FROM information_schema.COLUMNS
                      		WHERE column_name=fieldName
                      		and table_name=tableName
                      		and table_schema=dbName
                      		)
                      	THEN
                      		set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName,
                      			' DROP ',fieldName);
                      		prepare stmt from @ddl;
                      		execute stmt;
                      	END IF;
                      end;
                      //
                      delimiter ';' ;

Select all Open in new window

I hope these help ... and if you'll send  me some more tips, I'll add them.  I'll probably add some more anyway as I keep working in MySQL.