为何MySQL中SET语句可同时使用=与:=作为赋值运算符？

Great question! Let's break this down clearly, since it's a common point of confusion with MySQL's syntax:

• Context eliminates ambiguity
  MySQL uses the surrounding statement context to interpret what = is supposed to do. In a SET statement (or the SET clause of an UPDATE), the only logical operation here is assigning a value to a variable or column—there's no room for confusion between assignment and comparison. So MySQL lets you use = as an assignment operator here, just like the explicit :=.

• := exists for ambiguous scenarios
  Outside of SET (like in a SELECT or WHERE clause), using = would be ambiguous. For example:

  -- This does a comparison, returns 1 or 0 based on whether @var equals 1
  SELECT @var = 1;
  -- This explicitly assigns 1 to @var, returns the assigned value
  SELECT @var := 1;
  

  The := operator was introduced to handle these cases where MySQL can't tell if you want to compare values or assign a value. But since SET has no such ambiguity, both operators work seamlessly.

• Backward compatibility keeps it working
  MySQL has supported using = for assignment in SET statements since early versions. Retaining this behavior ensures older code doesn't break when newer syntax features (like :=) are added.

Looking at your stored procedure example:

DELIMITER $$ 
CREATE DEFINER=`root`@`localhost` PROCEDURE `substringExample`() 
BEGIN 
  DECLARE x varchar(7); 
  DECLARE num int; 
  DECLARE inc int; 
  SET inc:= 1; -- Uses := for assignment
  WHILE inc<1400 DO 
    SELECT SUBSTRING(USER_TEMP_NUM, 8, 13) AS ExtractString INTO x FROM USER_REGISTRATION_DETAILS where sl_no=inc; 
    SET num= CONVERT(x,int); -- Uses = for assignment, works identically
    -- ... rest of your code
END $$
DELIMITER ;

Both SET lines do exactly the same thing: assign a value to the declared variable. You could swap = and := in those lines and the procedure would run without any changes in behavior.

内容的提问来源于stack exchange，提问作者Bikrant Jena