DEV Community

kakisoft
kakisoft

Posted on • Updated on

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

Top comments (4)

Collapse
 
geraldew profile image
geraldew

Interesting, not that I use MySQL anywhere. It probably doesn't occur to me to expect "integer" data types to have consistency across SQL/relational engines and that I would always use DECIMAL for reliable migrations. I feel I've known about binary-coded-decimal (BCD) formats for longer than I've done data work, but I really can't recall where from - FORTRAN IV perhaps?

I suspect a reality is that the "data" world has always been a bit slapdash about data formats. For example, there are many situations where something like DECIMAL(18,0) will be used for an ID value, which is numeric but never actually used as a number - i.e. is never added or multiplied. Thus a BCD gets used just for holding digits - and that there are code libraries ready for calculating with them is a moot point.

Of course, a BCD is more compact in bits than is the equivalent text, but it's a long time since I've heard anyone worry about that amount of storage distinction.

Collapse
 
kakisoft profile image
kakisoft

Thank you for your reply!
I hardly use decimal, so your perspective is really interesting.
I found it informative, because I didn't concern about storage much.

Collapse
 
geraldew profile image
geraldew

To be honest, by now, storage has become a weird topic. Depending on the situation:

  • storage is cheap, plentiful, someone-else's-problem - and so isn't worth the time, effort and inconvenience of worrying about
  • data is BIG and so every minor detail of storage will be magnified at great scale and so great attention should be paid to it.

My personal maxim in data work is: correct result first, optimisations second. If an action or process is only being done to get a result once, then you might never optimise it all.

  • that said, there's merit in optimisation that assists in achieving correctness.
Collapse
 
greek1992 profile image
Greek1992

this was helpfull