To equals or not to equals

That's just funny...
I just had a discussion with Beat, Giuseppe and Markus about the most preferable to write assignments in MySQL.

MySQL has a couple of ways to write an assignment:

inside the SET clause of an UPDATE statement to assign values to database columns

UPDATE sakila.film SET original_language_id := 1

inside a SET statement in to assign values to user defined variables or (in a stored routine context) local variables, parameters and trigger NEW.% values.

SET v_done := TRUE;

inside the SELECT list, where you can assign expressions to user-defined variables.

SELECT @a := col1 FROM table1

using a SELECT..INTO statement.

SELECT col1 INTO @a FROM table1

Our discussion mainly dealt with the issue of the assignment operator: should one write :=, or rather =?

Now, for the SELECT @a := col1 FROM table1 case, it's simple and clear cut: this is MySQL proprietary syntax, and writing SELECT @a = col1 FROM table1 simply is a different thing here: = means comparison, := means assignment.

Given that, it seems quite natural to always use := for assignment, and = for comparison right? It would make me happy, as I'm quite used to this distinction in Oracle PL/SQL. The only thing that bothered me a little bit is: should I use := for the SET clause in ordinary UPDATE statements too? That would certainly be different in orcle?

(As it seems, := is allowed in MySQL, even in UPDATE...SET statements)

Anyway, as it turns out, this is what the SQL standard (ISO/IEC 9075-4:2003, 13.5, assignment statement) sais:


[singleton variable assignment] ::=
SET [assignment target] [equals operator] [assignment source]


Which I happen to find funny! So, yeah right, assignment uses the [equals operator]....LOL!