MySQL – How to Change Delimiter in MySQL – Supporting GO Keyword of SQL Server in MySQL

I work with both the technologies – SQL Server and MySQL and I enjoy working with both of them. In industry you will find few people talking about how one technology is better than other but the reality is that both of them have their greater share. Sometimes when I am working with SQL Server, I have to switch back to MySQL to check if the same query works there as well. In SQL Server I use GO as a statement terminator but in MySQL the statement terminator is semicolon (;). Since, I started to work with both the technologies, I have also started practice to use semicolon in SQL Server. However, old habits are hard to forget and there are times, when I end up with GO as a statement terminator instead of semicolon.

Now when I copy and paste my SQL scripts from SQL Server to MySQL, I end up with GO statement in MySQL which gives error 1004. See below attached image.

There are two workaround for this situation.

1) Find and Replace

I can easily find and replace all the GO keywords with semicolon and later on execute the script in MySQL. However, the problem is that as GO statement is in the new line the semicolon also goes to next line and it looks ugly. I later on figured out that I should also replace the newline+GO with semicolon but this is not a straightforward way to do so.

2) Change the DELIMITER in MySQL for the batch

I find this option very handy as I do not have to change my code which I have pasted from SQL Server to MySQL and can test my query with MySQL. I can easily move back from MySQL to SQL Server and I again do not have to change anything. This is temporary solution but works great for my situation.

I change my delimiter to GO for a batch in MySQL and later on change it back to semicolon. This works beautifully for me.

DELIMITER GO

SELECT 1
GO
SELECT 2
GO

DELIMITER ;

See the result of above query in attached image.

Let me know if there is any other trick I am missing.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

One thought on “MySQL – How to Change Delimiter in MySQL – Supporting GO Keyword of SQL Server in MySQL

  1. This is nice and clean approach. But in case someone is happy with find and replace, he may use notepad++ which allows to select multiple lines and then can replace it with semi-colon in a single step for whole file :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s