1

I have seen this code in CodeIgniter docs.

<?php

$fields = [
    'id' => [
        'type'           => 'INT',
        'constraint'     => 5,
        'unsigned'       => true,
        'auto_increment' => true,
    ],
    'title' => [
        'type'       => 'VARCHAR',
        'constraint' => '100',
        'unique'     => true,
    ],
    'author' => [
        'type'       => 'VARCHAR',
        'constraint' => 100,
        'default'    => 'King of Town',
    ],
    'description' => [
        'type' => 'TEXT',
        'null' => true,
    ],
    'status' => [
        'type'       => 'ENUM',
        'constraint' => ['publish', 'pending', 'draft'],
        'default'    => 'pending',
    ],
];

The id field will translate to INT(5). I think an integer should be large enough for id. What is the point of adding length?

I generally use INT. I am wondering what is the best practice.

Elavarasan
  • 21
  • 3
  • 2
    There is none. MySQL allows the length specifier for integer types, but it is meaningless. If you require smaller integers, use one of the smaller integer types. – Sammitch Aug 24 '23 at 23:21
  • In MySQL, when you specify INT(5), it does not actually limit the range of values that can be stored in the column. It only affects the display width when the column is displayed. – Rinsad Ahmed Aug 25 '23 at 00:26

2 Answers2

3

INT(5) is a 4 byte integer with a display width of 5. The display width is only be used, if the integer column is defined with the additional ZEROFILL option.

CREATE TABLE t1(a INT(5) ZEROFILL);
INSERT INTO t1 VALUES (1),(23),(589);
SELECT a FROM t1;
+-------+
| a     |
+-------+
| 00001 |
| 00023 |
| 00589 |
+-------+
Georg Richter
  • 5,970
  • 2
  • 9
  • 15
0

the defined length is not like a limit or maximum length of your field, you can use length 5 and still be able to insert an id with 20 length.

(the answer is finished, the rest is just an extra explanation)

you can ignore it, it's actually used for (visual representations) like when you use some database tool it leave a space for 5 characters for example but it doesn't mean it can't be a value that have more then 5. for example if we add a column of name you can set the length foe example to 10. it means if even the table data are displayed in some database tool, or for visual representations of the data, it will shows only the first 10 characters as an act of good looking and good data reading, but it can be a name that has more than 10 but it will just be ellipsised.

on the other hand if you are concerned about the rang of the field you should use the appropriate integer type (INT, BIGINT...).

HichemTech
  • 389
  • 2
  • 6