What is the difference between = and: = assignment operators?

MySQLMySQLi Database

Actually, they both are assignment operator and used to assign values but the significant difference between them is as follows −

= operator assigns a value either as a part of the SET statement or as a part of the SET clause in an UPDATE statement, in any other case = operator is interpreted as a comparison operator. On the other hand, := operator assigns a value and it is never interpreted as a comparison operator.

mysql> Update estimated_cost1 SET Tender_value = '8570.000' where id = 2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> Update estimated_cost1 SET Tender_value := '8575.000' where id = 2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

In the above two queries, we have used the = operator as well as: = operator to UPDATE the value of the table.

mysql> Set @A = 100;
Query OK, 0 rows affected (0.01 sec)

mysql> Select @A;
+------+
| @A   |
+------+
| 100  |
+------+
1 row in set (0.00 sec)

mysql> Set @B := 100;
Query OK, 0 rows affected (0.00 sec)

mysql> Select @B;
+------+
| @B   |
+------+
| 100  |
+------+
1 row in set (0.00 sec)

In the above two queries, we used = operator and: = operator to assign a value to a user variable. We can see that in both the situations = operator and: = operator have the same usage and functionality. But in the following query = operator, works as a comparison operator and give the result as ‘TRUE’ i.e. both the user variables @A and @B are having same values.

mysql> Select @A = @B;
+---------+
| @A = @B |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)
raja
Published on 21-Feb-2018 10:51:02
Advertisements