0

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?

wolfcall
  • 678
  • 1
  • 8
  • 18

2 Answers2

3

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Strings are converted to floating point number, not to int. And conversion happens as long as the expression can be interpreted as a number, which includes characters other than digits. – Shadow Apr 02 '17 at 21:13
  • @Shadow, thanks, I have edited the above to be more accurate. Can you please reverse your downvote? – Bill Karwin Apr 02 '17 at 22:10
2

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.

Shadow
  • 33,525
  • 10
  • 51
  • 64