Laravel, MySQL - column_type is set to "int (11)" even though the size of int was specified.

kakisoft

kakisoft

Posted on May 24, 2022

Laravel, MySQL - column_type is set to "int (11)" even though the size of int was specified.

Environment
MySQL version : 5.7
Laravel version : 8.16.1
PHP version : 7.4.7

When adding table columns in the migration file in Laravel, int size can be specified like this.

Schema::table('projects', function (Blueprint $table) {
    $table->integer('category_code')->length(3);
});
Enter fullscreen mode Exit fullscreen mode

However, looking at the actual generated MySQL schema, column_type is "int (11)".
What's going on? Did I make a mistake?

So, I looked it up, and found something like this.

https://stackoverflow.com/questions/25772759/schema-builder-length-of-an-integer

If you're using MySQL, you can't specify the length of an integer column.
You can only choose between one of the available integer types, described at http://dev.mysql.com/doc/refman/5.1/en/integer-types.html

In short, apparently "if you are using MySQL, you cannot specify the length".

This means that for some of the MySQL types, the size is already predetermined, and it cannot be changed as you like.
The table is here.

Type Storage (Bytes) Minimum Value Signed Minimum Value Unsigned Maximum Value Signed Maximum Value Unsigned
TINYINT 1 -128 0 127 255
SMALLINT 2 -32768 0 32767 65535
MEDIUMINT 3 -8388608 0 8388607 16777215
INT 4 -2147483648 0 2147483647 4294967295
BIGINT 8 -263 0 263-1 264-1

Conclusion

"If you use int in MySQL, it will always be" int (11) ", but that's just the way MySQL works, so please bear that in mind.

So what about tinyint?
When I looked it up, I found something like this.

Database: Migrations - Laravel - The PHP Framework For Web Artisans

The following column types can be modified: bigInteger, binary, boolean, date, dateTime, dateTimeTz, decimal, integer, json, longText, mediumText, smallInteger, string, text, time, unsignedBigInteger, unsignedInteger, unsignedSmallInteger, and uuid. To modify a timestamp column type a Doctrine type must be registered.

In short, it says
"Cannot change from int to tinyint"
(The opposite is possible)

If you can't do the opposite, it means that the rollback doesn't work properly, so you should avoid it.

In fact, I also suffered from unnecessary errors.

So, if you use int or bigint, it is OK to default length.
(Rather, there is no other way.)

Note

If you want to change from int to tinyint, it is possible to do so without Laravel syntax, but using alter table.
For example:

DB::statement("alter table projects modify category_code tinyint;");
Enter fullscreen mode Exit fullscreen mode
💖 💪 🙅 🚩
kakisoft
kakisoft

Posted on May 24, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related