Integer division in MySQL

mysql sql select sqldatatypes
biji buji picture biji buji · Jun 29, 2012 · Viewed 19.7k times · Source

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 )

Answer

djechlin picture djechlin · Jun 18, 2013

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.

Related questions

SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More
MySQL SELECT only not null values

Is it possible to do a select statement that takes only NOT NULL values? Right now I am using this: SELECT * FROM table And then I have to filter out the null values with a php loop. Is there a …

Read More
MySQL Select Query - Get only first 10 characters of a value

Ok, so here is the issue. I have a table with some columns and 'subject' is one of the columns. I need to get the first 10 letters from the 'subject' field no matter the 'subject' field contains a string with 100 …

Read More