What is the use of IGNORE_SPACE SQL mode?

MySQLMySQLi Database

The IGNORE_SPACE SQL mode can be used to modify how the parser treats function names that are whitespace-sensitive. Following are the cases in which we can use IGNORE_SPACE SQL mode −

Case-1  − When IGNORE_SPACE SQL mode is disabled

After disabling the IGNORE_SPACE SQL mode, the parser interprets the name as a function call when there is no whitespace between the name and the following parenthesis. This also occurs when the function name is used in a non-expression context. It can be understood from the following query −

mysql> Create table SUM(Id Int);
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near 'SUM(Id Int)' at line 1

Now we can use either whitespace or write the name in quotes to eliminate the error and cause the name to be treated as an identifier. Following statements did the same −

Create table SUM (id int);
Create table ‘SUM’(id int);
Create table ‘SUM’ (id int);

Case-2 − When IGNORE_SPACE SQL mode is enabled

When we enable this mode, the parser relaxes the requirement that there be no whitespace between the function name and the following parenthesis. For example, after enabling IGNORE_SPACE SQL mode both of the following function calls are legal −

Select SUM(Salary) from employee;
Select SUM (Salary) from employee;

But, in this case, the parser treats the function name as reserved words. It means that a space following the names no longer represents an identifier.

Published on 21-Feb-2018 12:51:31