32

I've been omitting the semicolon at the end of my MySQL queries recently, and it occurred to me that this might have possible negative effects maybe during server high load, caching etc. Are there any of such effects?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
rtuner
  • 2,362
  • 3
  • 25
  • 37
  • 5
    If the system is able to tell the end of the statement without the semi-colon, omitting them does no harm. If the system gets confused, it matters. Since you've been able to leave them off, there isn't apparently a problem. A lot depends on how you're writing your SQL. If you're writing single statements in, say, PHP and then sending them to MySQL for processing, the semi-colon is optional. – Jonathan Leffler Jul 02 '13 at 14:04
  • I think it's good coding style to do so. It also helps you when you migrate to other DBMS that do require them. –  Jul 02 '13 at 14:10
  • I don't understand the situations explained in the SQLServerCentral article at all. :( – rtuner Jul 02 '13 at 14:10
  • 1
    @rtuner - They don't apply in MySQL. MySQL doesn't support common table expressions, merge, or service broker. And even if it did support the first two there is no reason to expect the requirement to be the same as TSQL. – Martin Smith Jul 02 '13 at 14:11
  • 3
    FWIW, I like to be able to pull an echoed version of my query out of my code and paste it directly into a MySQL Command Line prompt; so I always include the terminator. – Strawberry Jul 02 '13 at 14:14

1 Answers1

26

If the system is able to tell the end of the statement without the semicolon, omitting them does no harm. If the system gets confused, it matters. Since you've been able to leave them off, there isn't apparently a problem. A lot depends on how you're writing your SQL. If you're writing single statements in, say, PHP and then sending them to MySQL for processing, the semicolon is optional.

You ask if it "might have possible negative effects maybe during server high load, caching etc." The answer to that is 'No'. If it has an effect, it is on the basic interpretation of what you meant, and the difference is almost inevitably between 'it works' and 'it does not compile, let alone run'. Effects such as load or caching are completely independent of the presence or absence of semicolons.

This answer applies fairly generally. There's a reference to an SQL Server question which suggests that SQL Server did not need semicolons but is being changed in more recent editions so that they are necessary. It applies to most other DBMS. SQL command interpreters working on an SQL script need to know the boundaries between SQL statements, and the semicolon is the standard way of representing that, though there are other conventions (I believe some use go and some use a slash / for the job). But fundamentally, it comes down to "does it work". If it does, then the semicolon was not necessary.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • Clarification on SQL Server: Semicolon is a statement terminator, and **go** is a batch terminator. Generally there can be several statements in a batch, though certain DDL statements, such as CREATE VIEW, require to be the only statement in their batch. – Pieter Geerkens Jul 03 '13 at 01:30
  • @PieterGeerkens: Thanks for the clarification. As you can tell, I'm not a user of MS SQL Server. – Jonathan Leffler Jul 03 '13 at 02:15