Setting the correct field types in a MySQL table is an important step in database design. The right data types will help optimize information storage and speed up query execution.
The main MySQL data types and recommendations for their use, including for migrations to Laravel.
Field types in MySQL
1. Numeric types
- INT
: Used for integers.
- TINYINT
: For very small integers (from -128 to 127 or from 0 to 255).
- SMALLINT
: For small integers (from -32,768 to 32,767).
- MEDIUMINT
: For medium integers (from -8,388,608 to 8,388,607).
- BIGINT
: For large integers (from -2^63 to 2^63-1).
- FLOAT
and DOUBLE
: For floating point numbers.
2. String types
- VARCHAR(n)
: For strings of variable length.
- CHAR(n)
: For fixed-length strings.
- TEXT
: For storing large texts.
- BLOB
: For storing binary data.
3. Date and time
- DATE
: For storing date (YYYY-MM-DD).
- TIME
: For storing time (HH:MM:SS).
- DATETIME
: For storing date and time (YYYY-MM-DD HH:MM:SS).
- TIMESTAMP
: Similar to DATETIME
, but automatically updated when the record changes.
4. Logical types
- BOOLEAN
: TINYINT(1)
is used for boolean values.
Recommendations for choosing data types
1. Use the minimum required data type for your needs.2. Use VARCHAR
for strings that can vary in length, and CHAR
for fixed strings.3. When using indexes, keep in mind that longer fields slow down operations.4. For prices, use DECIMAL
with the specified number of decimal places.
Example of a table with the correct data types
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
quantity INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Field types in Laravel
Examples of migrations with different data types
1. Numeric types
Schema::create('products', function (Blueprint $table) {
$table->id(); // This is the default BIGINT
$table->tinyInteger('rating')->nullable(); // TINYINT
$table->smallInteger('stock')->default(0); // SMALLINT
$table->integer('price'); // INT
$table->float('discount')->nullable(); // FLOAT
$table->double('price_with_tax', 15, 8); // DOUBLE
});
2. String types
Schema::create('users', function (Blueprint $table) {
$table->id(); // BIGINT
$table->string('name', 100); // VARCHAR(100)
$table->string('email')->unique(); // VARCHAR
$table->char('country_code', 2); // CHAR(2)
$table->text('bio')->nullable(); // TEXT
$table->binary('avatar')->nullable(); // BLOB
});
3. Date and time types
Schema::create('orders', function (Blueprint $table) {
$table->id(); // BIGINT
$table->date('order_date'); // DATE
$table->time('delivery_time'); // TIME
$table->dateTime('created_at')->useCurrent(); // DATETIME
$table->timestamp('updated_at')->nullable(); // TIMESTAMP
});
4. Logical types
Schema::create('settings', function (Blueprint $table) {
$table->id(); // BIGINT
$table->boolean('is_active')->default(true); // TINYINT(1)
});
Example of a full migration
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateProductsTable extends Migration
{
public function up()
{
Schema::create('products', function (Blueprint $table) {
$table->id(); // BIGINT
$table->string('name', 100); // VARCHAR(100)
$table->text('description')->nullable(); // TEXT
$table->decimal('price', 10, 2); // DECIMAL (10, 2)
$table->integer('quantity')->default(0); // INT
$table->timestamps(); // created_at and updated_at
});
}
public function down()
{
Schema::dropIfExists('products');
}
}
General recommendations
1. Use nullable()
: For fields that may not matter.
2. Use default(value)
: To set default values.3. Keep in mind the uniqueness: The unique()
method ensures the uniqueness of the value in the column.