How to fix ERROR 1101: BLOB/TEXT/JSON can't have default value in MySQL

The simplest solution is to remove the DEFAULT clause entirely. BLOB, TEXT, JSON, and GEOMETRY columns will automatically accept NULL unless you explicitly mark them as NOT NULL.

Replace:

CREATE TABLE users (
  id INT PRIMARY KEY,
  profile TEXT NOT NULL DEFAULT 'N/A'
);

With:

CREATE TABLE users (
  id INT PRIMARY KEY,
  profile TEXT NOT NULL
);

If your application requires a value, handle the default in your application code or use a trigger instead.

If you are using MySQL 8.0.13 or later, you can assign default values to BLOB, TEXT, GEOMETRY, and JSON columns, but only if the default is written as an expression (even if it's a literal value).

For JSON columns, use expression syntax:

CREATE TABLE settings (
  id INT PRIMARY KEY,
  config JSON DEFAULT (JSON_OBJECT())
);

For TEXT columns with literal defaults, wrap in parentheses:

CREATE TABLE posts (
  id INT PRIMARY KEY,
  content TEXT DEFAULT ('')
);

For empty arrays in JSON:

CREATE TABLE items (
  id INT PRIMARY KEY,
  tags JSON DEFAULT (JSON_ARRAY())
);

The key difference is the parentheses around the expression.

If you need automatic value initialization for LOB columns and you're on MySQL < 8.0.13 or need more control, create a BEFORE INSERT trigger:

CREATE TABLE documents (
  id INT PRIMARY KEY AUTO_INCREMENT,
  content LONGTEXT,
  metadata JSON
);

DELIMITER //
CREATE TRIGGER set_defaults_before_insert
BEFORE INSERT ON documents
FOR EACH ROW
BEGIN
  IF NEW.content IS NULL THEN
    SET NEW.content = 'Default document content';
  END IF;
  IF NEW.metadata IS NULL THEN
    SET NEW.metadata = JSON_OBJECT('created', NOW());
  END IF;
END //
DELIMITER ;

This approach gives you more flexibility and works across all MySQL versions.

If your data is relatively small (under 65,535 bytes), consider using VARCHAR instead of TEXT or VARBINARY instead of BLOB. These types support DEFAULT values:

-- Instead of TEXT with DEFAULT
CREATE TABLE posts (
  id INT PRIMARY KEY,
  excerpt VARCHAR(500) DEFAULT 'No excerpt provided'
);

-- Instead of BLOB with DEFAULT
CREATE TABLE files (
  id INT PRIMARY KEY,
  thumbnail VARBINARY(1000) DEFAULT 0x0000
);

VARCHAR(n) allows up to n characters, and VARBINARY(n) allows up to n bytes. This is a viable solution if your content fits within these limits.

In STRICT modes (like STRICT_TRANS_TABLES or STRICT_ALL_TABLES), this error is raised immediately. While not recommended, you can temporarily check your SQL mode:

SELECT @@sql_mode;

Do NOT disable STRICT_TRANS_TABLES permanently—it protects data integrity. Instead, fix your schema to comply with MySQL requirements.