CODEWITHSUNDEEP
mysqldata-comparison
wolfcall
wolfcall

Reputation: 896

How does mysql do multi-type comparison?

I was working on a project and due to a miscomprehension, we ended up comparing a stored int with a string in a MySql database. I ran a few test and it seems to work but I would like to know how MySql compares different datatypes.

Does it convert one to the other? If it does does it convert both to strings or to ints?

Upvotes: 0

Views: 890

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562891

When you use a string in an integer context, for example in an arithmetic expression or in a comparison to an integer, MySQL takes the numeric value of that string as a DOUBLE data type.

See https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

Demonstration:

mysql> create table foo as select 1+'1' as x;

mysql> show create table foo\G
CREATE TABLE `foo` (
  `x` double NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The numeric value of a string is the numeric value of any leading digit characters or other characters that make a floating-point number, like -+.e.

For example, the numeric value of '123abc' is 123.

Scientific notation is supported.

mysql> select 1 + '5e-2xyz' as n;
+------+
| n    |
+------+
| 1.05 |
+------+

If there are no leading characters that form a numeric value, the string's numeric value is 0.

Upvotes: 3

Shadow
Shadow

Reputation: 34294

Mysql manual has a complete section dedicated to this, called Type Conversion in Expression Evaluation.

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

If you compare an int with a string, then both values are converted to floating point number and compared thus.

Upvotes: 2

Related Questions