MySQL integer unit

Question

I have a MySQL table that has a product_id field (large integer)

1102330008 1102330025 1102330070 1103010009 1103010010 1103020006 ...

I want to select rows with product_id = 110301**** . I tried using this query:

 SELECT * FROM `product` WHERE (product_id/10000)=110301 

but it does not return any values ​​with this message:

MySQL returned an empty result set (ie zero rows). ( Query took 0.0005 sec )

Answer 1

The MySQL documentation says that LIKE also uses indexes, so I think you can also use:

 SELECT * FROM `product` WHERE product_id LIKE '110301____' -- four undersores as biziclop suggested 

edit: to fix your own request, you will need to use FLOOR() ( documentation ), because it leads to something like:

 1103010006 / 10000 

the result of which is 110301,0006 and not equal to 110301

Answer 2

Use the DIV operator.

 mysql> SELECT 5 DIV 2; -> 2 

Integer division. Like FLOOR (), but safe with BIGINT values. Wrong results may occur for non-integrated operands that exceed the BIGINT range.

Answer 3

 SELECT * FROM product WHERE product_id BETWEEN 1103010000 AND 1103019999 

If you want to create your request in PHP, you can build your request, for example

 $sql = " SELECT * FROM product WHERE product_id BETWEEN {$product_id_range}0000 AND {$product_id_range}9999 "; 

Answer 4

 SELECT * FROM `product` WHERE `product_id` >= 1103010000 AND `product_id` <= 1103019999 

Answer 5

You can use the MySQL Integer DIV division operator as follows:

 SELECT * FROM `product` WHERE (product_id DIV 10000)=110301 

Also safe for BIGINT.

Answer 6

The MYSQL documentation , I think, explains why this happens;

Division is calculated using BIGINT arithmetic only when executed in a context where its result is converted to an integer.

So, if you convert the result to an integer, it may work.

Edit: Try

 SELECT * FROM `product` WHERE cast((product_id/10000) as int) = 110301