How to ADD COLUMN in MariaDB

Galaxy Glossary

How do I add a column to an existing table in MariaDB?

ALTER TABLE ... ADD COLUMN adds one or more columns to an existing MariaDB table without recreating it.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does ALTER TABLE ADD COLUMN do in MariaDB?

ALTER TABLE ... ADD COLUMN inserts a new field into an existing table definition, immediately making the column available for queries, inserts, and updates without losing existing data.

How do I add a column with a default value?

Include the DEFAULT clause when defining the new column. MariaDB back-fills existing rows with the default, ensuring non-NULL data in legacy records.

Can I add multiple columns at once?

Yes—separate each ADD COLUMN clause with a comma inside the same ALTER TABLE statement. This approach avoids multiple locks and speeds deployment.

When should I specify AFTER or FIRST?

Use AFTER existing_column or FIRST to control physical order. Logical order rarely matters to queries, but ordering helps human readers and some GUI tools.

Best practices for adding columns safely

Schedule ALTER TABLE during low-traffic windows, add defaults or NOT NULL carefully, and always back up the table before structural changes.

Common mistakes and how to avoid them

Omitting a default on a NOT NULL column fails if rows exist. Also, forgetting to lock long-running alters can impact production workloads.

Why How to ADD COLUMN in MariaDB is important

How to ADD COLUMN in MariaDB Example Usage


-- Add a JSON metadata column to Orders and fill with an empty object
aLTER TABLE Orders
    ADD COLUMN metadata JSON NOT NULL DEFAULT ('{}'),
    ADD COLUMN last_updated TIMESTAMP NULL AFTER order_date;

How to ADD COLUMN in MariaDB Syntax


ALTER TABLE table_name
    ADD [COLUMN] column_name data_type
        [NOT NULL | NULL]
        [DEFAULT default_value]
        [AFTER existing_column | FIRST]
  , ADD column_name2 data_type ...;

-- Example
ALTER TABLE Products
    ADD COLUMN discontinued TINYINT(1) NOT NULL DEFAULT 0 AFTER stock;

Common Mistakes

Adding a NOT NULL column without a DEFAULT causes ERROR 1364 if rows already exist. Fix by specifying a DEFAULT or allowing NULL.
Running many single ADD COLUMN statements locks the table multiple times. Combine columns in one ALTER TABLE to minimize downtime.

Frequently Asked Questions (FAQs)

Does ADD COLUMN lock the table?

Yes, traditional ALTER TABLE commands acquire a metadata lock. Large tables may experience downtime. Use pt-online-schema-change or gh-ost for hot alters.