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;");
Top comments (4)
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.
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.
To be honest, by now, storage has become a weird topic. Depending on the situation:
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.
this was helpfull