CODEWITHSUNDEEP
mysqlsqlselectsqldatatypes
biji buji
biji buji

Reputation: 465

Integer division in MySQL

I have a MySQL table which has a product_id field (big integer)

1102330008 1102330025 1102330070 1103010009 1103010010 1103020006 ...

I want to select rows which have product_id = 110301****. I tried to use 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 (i.e. zero rows). ( Query took 0.0005 sec )

Upvotes: 23

Views: 29535

Answers (6)

djechlin
djechlin

Reputation: 60748

Use the DIV operator.

mysql> SELECT 5 DIV 2;
    -> 2

Integer division. Similar to FLOOR(), but is safe with BIGINT values. Incorrect results may occur for noninteger operands that exceed BIGINT range.

Upvotes: 49

Perry
Perry

Reputation: 141

You can use the MySQL Integer division operator DIV like this:

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

Also safe for BIGINT.

Upvotes: 3

David Manheim
David Manheim

Reputation: 2626

MYSQL Documentation, I think, explains why this occurs;

A division is calculated with BIGINT arithmetic only if performed 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

Upvotes: 1

biziclop
biziclop

Reputation: 14596

SELECT *
FROM product
WHERE product_id BETWEEN 1103010000
                     AND 1103019999

If you want to create your query in PHP, then you can construct your query like

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

Upvotes: 8

Teneff
Teneff

Reputation: 32158

MySQL documentation says that LIKE also uses indexes so I think you could also use:

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

edit: to fix your own query you would need to use FLOOR() (documentation) because it results something like:

1103010006 / 10000

which results 110301,0006 and it's not equal to 110301

Upvotes: 4

John Conde
John Conde

Reputation: 219804

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

Upvotes: 2

Related Questions