如何定义字段默认值？及SQLSTATE[HY000]: 1364错误修复求助

Hey Julia, let's break down your two database questions and get you sorted out!

Setting default values depends on whether you're using raw SQL or an ORM (like Laravel, which your error message hints at). Here's how to do both:

Using Raw SQL #

For MySQL/MariaDB #

When creating a table or modifying an existing column, use the DEFAULT keyword to set your desired value:

-- Create table with default value
CREATE TABLE people (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    note TEXT DEFAULT '', -- Empty string as default
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add default to an existing column
ALTER TABLE people MODIFY COLUMN note TEXT DEFAULT '';

For PostgreSQL #

Syntax is similar, just ensure you cast values to the correct type if needed:

-- Create table with default value
CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    note TEXT DEFAULT ''::TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Update existing column's default
ALTER TABLE people ALTER COLUMN note SET DEFAULT '';

For SQLite #

SQLite supports defaults, though it has minor limitations (like CURRENT_TIMESTAMP only working with TIMESTAMP types):

-- Create table with default value
CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    note TEXT DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Using an ORM (e.g., Laravel) #

Since your error message looks like it came from Laravel's query builder, here's how to set defaults in migrations:

// Create a new table with default value
public function up()
{
    Schema::create('people', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->text('note')->default(''); // Set empty string as default
        $table->timestamps();
    });
}

// Update an existing column to add a default
public function up()
{
    Schema::table('people', function (Blueprint $table) {
        $table->text('note')->default('')->change();
    });
}

This error happens because your note column is marked as NOT NULL but has no default value—so when you insert a row without providing note, the database doesn't know what value to use. Here's how to fix it:

Step 1: Check the Column's Current Setup #

First, confirm the column's constraints with a quick query:

-- MySQL/MariaDB
DESCRIBE people;

-- PostgreSQL
\d people;

-- SQLite
PRAGMA table_info(people);

You’ll see note has Null set to NO and no value in the Default column.

Step 2: Add a Default Value via SQL #

Run this query to update the column with a default (adjust based on your database):

-- MySQL/MariaDB
ALTER TABLE people MODIFY COLUMN note TEXT NOT NULL DEFAULT '';

-- PostgreSQL
ALTER TABLE people ALTER COLUMN note SET DEFAULT '';
-- If the column isn't already NOT NULL, add that too:
ALTER TABLE people ALTER COLUMN note SET NOT NULL;

-- SQLite (note: SQLite can't modify existing columns directly; you'll need to recreate the table or use a workaround)
CREATE TABLE people_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    note TEXT NOT NULL DEFAULT '',
    email TEXT,
    user_id INT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);
INSERT INTO people_new SELECT id, name, '', email, user_id, created_at, updated_at FROM people;
DROP TABLE people;
ALTER TABLE people_new RENAME TO people;

Step 3: Adjust Your ORM Settings (If Using Laravel) #

If you’re using Laravel, make sure these two things are set:

1. Migration: Ensure your migration includes ->default('') for the note column, then run the migration:

   php artisan migrate
   # For a modification migration, run:
   php artisan migrate:refresh --path=/database/migrations/[your-migration-file-name].php
   

2. Model Batch Assignment: Add note to your model’s $fillable array so it’s included in mass assignments:

   class Person extends Model
   {
       protected $fillable = ['name', 'email', 'user_id', 'note'];
       // Or use $guarded = []; to allow all fields
   }
   

3. Alternative: Model-Level Default: If you don’t want to modify the database, set a default directly in the model:

   class Person extends Model
   {
       protected $attributes = [
           'note' => '',
       ];
   }
   

   This will automatically set note to an empty string whenever you create a new Person instance without specifying it.

Step 4: Test the Insert Again #

Once you’ve made these changes, re-run your insert statement—it should now work without errors, as the database will use the default value for note.

内容的提问来源于stack exchange，提问作者Julia Cassemiro