Laravel, MySQL - column_type is set to "int (11)" even though the size of int was specified.
kakisoft
Posted on May 24, 2022
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);
});
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;");
Posted on May 24, 2022
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.