Browse All Articles
> 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.
What does the silly table look like? What are it's fields and what are their types?
exec sp_help tablename
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';
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';
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 ( ... );
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;
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:
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)
IF EXISTS (
SELECT * FROM information_schema.COLUMNS
set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName,
' DROP ',fieldName);
prepare stmt from @ddl;
delimiter ';' ;
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.