CODEWITHSUNDEEP
mysqlsqlstring
Eddie Martinez
Eddie Martinez

Reputation: 13910

what do single quotes do in mysql

I am updating a float column in MySQL and both of the following syntax' work. 

UPDATE t1 SET col1 = 0;

UPDATE t1 SET col1 = '0';

Isn't the bottom one supposed to break as I am passing a string to a float column ?

According to the MySQL documentation a single quote is a string literal.

9.1.1. String Literals A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters. Examples:

'a string' "another string" Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:

'a string' 'a' ' ' 'string'

Upvotes: 0

Views: 93

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562911

Normally single quotes delimit string literals or datetime literals.

When you evaluate a string in a numeric context, MySQL takes the numeric value from the leading digits in the string.

mysql> select 0 + '123';
+-----------+
| 0 + '123' |
+-----------+
|       123 |
+-----------+
1 row in set (0.00 sec)

If there are additional characters in the string besides digits, they are truncated before the numeric value is calculated. This generates a warning, but the expression still works.

mysql> select 0 + '123abc';
+--------------+
| 0 + '123abc' |
+--------------+
|          123 |
+--------------+
1 row in set, 1 warning (0.01 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: '123abc'

Upvotes: 2

Nikolai Samteladze
Nikolai Samteladze

Reputation: 7797

String '1' is converted to float and then assigned to your column.

Upvotes: 1

Related Questions