Geek Answers Handbook

Is it better to use "null" or "-1" to indicate "infinite" in integer database columns

I often have fields in my database that store "number or infinity." Examples may be maxFileSize, maxUsersetc. It should be possible to set a maximum value or simply not limit it at all. Currently, I sometimes use it nullto mean "infinite / no limit", and sometimes I use it -1.

I was wondering if there was a reason to use one or the other. For the record, I work with PHP / MySQL / Doctrine / Symfony, if that matters.

Edit :

I do not ask for opinions, but for the facts (as you see in the answers already received) related to the use of -1or null. These may include speed, query complexity, database size, etc.

+4
source share
4 answers

If you intend to use an integer to store a value and want to keep a really large value, use 2,147,483,647. This is the greatest value that you can put in a field, so that it is close to infinity, as the field allows.

-1 NULL. , , . NULL , . :

where users < maxusers or maxusers is null
where users < maxusers or maxusers = -1

. , , .

, , . " " : "9999-01-01" "4195-01-01". - . , , , NULL.

+5

imho

null - , . -1 - . SUM(), AVG() .. .

, , . null - null, , null, empty .

-1, , .

, 1 1 0, true false?

- , .

/ - empy, null.

+1

:

file_quota int NOT NULL DEFAULT 50000,
file_quote_is_limited boolean NOT NULL DEFAULT true

MySQL, , , NULL. :

  • , NULL , , . ( ..).
  • if (null == $obj->getFileQuota()) { . .
  • , , . , , :
    where file_quota < 50000 or file_quota is null

    where file_quota < 50000 or ! file_quota_is_limited ( , )
  • $usage = $usedFiles / $user->getFileQuota();, , .
  • , .
+1

NULL " ", . .

" " -1 , , , , .

, min max NULL, - .

0

More articles:


All Articles