<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Database Developers' Quick-Reference to MySQL DDL

Published on
9,173 Points
3,173 Views
Last Modified:
Approved
Community Pick
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

Open in new window

MySQL:
Describe tablename;

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'; 

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'; 

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 ( ... ); 

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; 

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 ';' ;

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.
0
Comment
0 Comments

Featured Post

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Join & Write a Comment

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month