What Does the Colon Equals Operator “:=” Mean in MySQL?

MySQL is a popularly used DBMS that supports various assignment operators to compare or assign values to variables or table fields/columns. One such operator is the “Colon Equals” operator “:=” which assigns a particular value to a variable. The assigned values can be accessed and utilized anywhere in the query. The “:=” operator is specifically used when a user has to store and reuse calculated or manipulated value/data.

This post will explain what the “colon equals” operator “:=” is and how it works in MySQL.

What Does the Colon Equals Operator “:=” Mean in MySQL?

In MySQL, the colon equal “:=” operator refers to an assignment operator. It is used along with the “SET” statement/keyword to assign a value to a variable, as shown in the following syntax:

The equal-to operator “=” can also be used to perform the same functionality.

Example 1: How Does the Colon Equals Operator Work With Numeric Data?
In the below-provided code snippet, a couple of variables are declared using the “equal to” and “Colon Equals Operator”:

The given snippet indicates that the provided values have been successfully assigned to the given variables:

To fetch the variable’s values, all you have to do is execute the SELECT statement along with variable names:

The SELECT query retrieves the assigned variable values:

In the following query, the equality of the given variables is checked using the “=” operator:

The “0” in the output snippet clarifies that both variables are not equal:

Use the given query to assign the value of the “num2” variable to the “num1” variable:

Now, execute the SELECT statement one more time to fetch the variable’s values:

The output snippet confirms that the value of “num1” is successfully assigned to the “num2” variable:

Example 2: How Does the Colon Equals Operator Work With String Data?
MySQL’s Colon Equals Operator works with string data as efficiently as with numeric data:

In this example code, first, a string value “linuxhint” is assigned to a variable named “str”, and then it is fetched using SELECT query:

Example 3: How Does the Colon Equals Operator Work With Table’s Data?
This example illustrates the usage of Colon Equals Operator on the table’s data:

In the above code, the “COUNT(*)” function is utilized to calculate the number of rows of the “linuxhint_products” table. The row count is assigned to a variable named “count” using the “:=” operator. Finally, the rows count is fetched using the SELECT statement:

Similarly, in the following code snippet, the “-” operator is used to subtract “10” from the “price” column of the “linuxhint_products” table. The subtracted/discounted price is assigned to the “discount_price” variable using the “:=” operator:

The given output snippet verifies the working of the colon equals operator in MySQL:

That’s all about the “Colon Equals Operator” in MySQL.

Conclusion

In MySQL, the colon equal “:=” operator represents an assignment operator that is utilized with the help of the “SET” statement to assign values to variables. The assigned variable values can be accessed and utilized anywhere in the query. The stated operator is specifically used when a user has to store and reuse calculated or manipulate value/data. This post has illustrated the use of the “colon equals” operator in MySQL using suitable examples.