Przejdź do treści
Biblioteka skilli
Programowanie · Nauka i wyjaśnianie

mariadb-create-table

/mariadb-create-table

Podaje składnię i zachowanie MariaDB przy CREATE TABLE: atomowe CREATE OR REPLACE, kolumny niewidoczne, tabele wersjonowane i kodowanie utf8mb4.

Repozytorium
ai-plugins ↗
Licencja
LGPL
Ostatnia zmiana repozytorium
12.11.2026

Ocena i bezpieczeństwo

100/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść35 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
1
Skrypty
0
Komentarze HTML
0
Adresy URL w SKILL.md
1
Przestarzałość

Brak sygnałów. Repozytorium zmienione 12.11.2026, bez odwołań do wycofanych modeli i API.

Licencja

LGPL, otwarta.

Co robi i kiedy

Podaje składnię i zachowanie MariaDB przy CREATE TABLE: atomowe CREATE OR REPLACE, kolumny niewidoczne, tabele wersjonowane i kodowanie utf8mb4.

Oryginalny opis (EN)

MariaDB-specific syntax and behavior for CREATE TABLE — atomic CREATE OR REPLACE, INVISIBLE / COMPRESSED columns, system-versioned and application-time PERIOD clauses, IGNORED indexes, table-level encryption, page compression, sequence and Aria table options, and the utf8/utf8mb4 trap. Use when writing, generating, or reviewing CREATE TABLE statements that target MariaDB.

Jak działa

  1. What LLMs Often Miss
  2. CREATE OR REPLACE
  3. Temporal Tables — PERIOD FOR …
  4. Column-Level MariaDB Extensions
  5. Index Definitions — MariaDB-Specific Options
  6. Table Options — MariaDB-Specific or Often-Confused
  7. Atomic DDL
  8. See Also
Sekcje z nagłówków SKILL.md, w oryginale · 125 linii, 1 251 słów
Podgląd SKILL.md
---
name: mariadb-create-table
description: "MariaDB-specific syntax and behavior for CREATE TABLE — atomic CREATE OR REPLACE, INVISIBLE / COMPRESSED columns, system-versioned and application-time PERIOD clauses, IGNORED indexes, table-level encryption, page compression, sequence and Aria table options, and the utf8/utf8mb4 trap. Use when writing, generating, or reviewing CREATE TABLE statements that target MariaDB."
---

# CREATE TABLE in MariaDB

*Last updated: 2026-06-01*

This skill covers the **delta between standard SQL `CREATE TABLE` and MariaDB's**. It assumes the agent already knows standard `CREATE TABLE name (col type, …)`. Everything below documents MariaDB-specific syntax, table options, column attributes, and behaviors.

> **Default context:** Assume MariaDB **11.8 LTS** (GA May 2025) unless the user specifies another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.

## What LLMs Often Miss

| If the agent writes / suggests… | …prefer the MariaDB form |
|---|---|
| `DROP TABLE x; CREATE TABLE x …` | `CREATE OR REPLACE TABLE x …` (from 13.2 atomic by default: the old table is kept under a backup name until the new one exists, so there is no window where the table is missing; before 13.2, or when the old table is dropped first, there is) |
| `CHARSET = utf8` | `CHARSET = utf8mb4` — `utf8` is an alias for the legacy 3-byte form (`utf8mb3`) and cannot store 4-byte characters (most emoji, some CJK) |
| Manual `created_at` / `updated_at` audit columns | `CREATE TABLE … WITH SYSTEM VERSIONING` — see the `mariadb-system-versioned-tables` skill |
| `ROW_FORMAT = COMPRESSED` for "modern" disk savings | That's the older InnoDB compressed row format. For new tables on modern InnoDB/Aria, prefer `PAGE_COMPRESSED = 1` plus optional `PAGE_COMPRESSION_LEVEL = N` |
| Generated column with `STORED` | Works, but MariaDB's historical keyword is `PERSISTENT`. Both are accepted; `PERSISTENT` shows up in older code and in `SHOW CREATE TABLE` output |
| Foreign keys on a MyISAM / Aria table | Silently parsed and discarded — only InnoDB enforces foreign keys |
| `IF NOT EXISTS` on a table with a different schema | Skips creation and emits a **warning** (not error); the existing table is left untouched even if its definition differs |
| Wrapping `CREATE TABLE` in a transaction | DDL triggers an implicit commit. There is no way to roll back a `CREATE TABLE`; rely on atomic DDL (below) for crash safety instead |

## CREATE OR REPLACE

```sql
CREATE OR REPLACE TABLE t (id INT PRIMARY KEY);
```

- From 13.2, atomic by default: the old table is renamed to a temporary backup and restored if the `CREATE` fails. Before 13.2, and when the old table is dropped first (`drop_before_create_or_replace=ON`, `TEMPORARY` tables, views, engines with expensive rename such as S3, replicas), it is equivalent to `DROP TABLE IF EXISTS t; CREATE TABLE t …`.
- Cannot be combined with `IF NOT EXISTS`.
- Before 13.2, and when the old table is dropped first, it is crash-safe but **not fully atomic** in the strict sense — recovery picks one side cleanly, but observers mid-statement may see the table missing. Plain `CREATE TABLE` *is* fully atomic (since 10.6.1).

## Temporal Tables — `PERIOD FOR …`

MariaDB supports three temporal forms via the `PERIOD FOR` clause:

```sql
-- System-versioned (transaction time, automatic history):
CREATE TABLE t (id INT, val INT) WITH SYSTEM VERSIONING;

-- Application-time period (business-valid time, application-managed):
CREATE TABLE t (
  id INT, val INT,
  valid_from DATE, valid_to DATE,
  PERIOD FOR effective (valid_from, valid_to)
);

-- Bitemporal (both at once):
CREATE TABLE t (
  id INT, val INT,
  valid_from DATE, valid_to DATE,
  PERIOD FOR effective (valid_from, valid_to)
) WITH SYSTEM VERSIONING;
```

For querying history, partitioning by `SYSTEM_TIME`, or removing old history, defer to the **`mariadb-system-versioned-tables`** skill.

Specific columns can be excluded from versioning:

```sql
CREATE TABLE t (
  id INT,
  login_counter INT WITHOUT SYSTEM VERSIONING
) WITH SYSTEM VERSIONING;
```

> **UPDATE behavior note:** for timestamp-based system versioning, a new history row is created for every `UPDATE` that touches a versioned column, even if no column value actually changes. See `mariadb-system-versioned-tables` for details.

## Column-Level MariaDB Extensions

| Clause | Effect |
|---|---|
| `col VARCHAR(N) COMPRESSED` | Per-value zlib compression on the column (transparent to clients) |
| `col INT INVISIBLE` | Excluded from `SELECT *`; explicit `SELECT col` still works; `INSERT` without column list skips it |
| `DEFAULT (expr)` | Defaults can be any deterministic expression — `DEFAULT (1+1)`, `DEFAULT(NOW() + INTERVAL 1 YEAR)`, `DEFAULT USER()` |
| `col INT GENERATED ALWAYS AS (expr) PERSISTENT` | Stored generated column. `PERSISTENT` is MariaDB's keyword; `STORED` is also accepted as an alias. Use `VIRTUAL` (default) for computed-on-read |
| `col INT GENERATED ALWAYS AS (expr) VIRTUAL` | Computed-on-read generated column (no storage cost, evaluated per query) |
| `col VECTOR(N)` *(since 11.7)* | Fixed-length float vector column — see the `mariadb-vector` skill |

## Index Definitions — MariaDB-Specific Options

```sql
INDEX idx_name (col1, col2) [index_type] [index_option …]
```

| Option | Meaning |
|---|---|
| `IGNORED` / `NOT IGNORED` *(since 10.6)* | Optimizer pretends the index doesn't exist (still maintained on writes). Use to "soft-disable" an index before dropping it |
| `INVISIBLE` / `VISIBLE` | Optimizer ignores the index; DML still updates it |
| `COMMENT '…'` | Free-text annotation, visible in `INFORMATION_SCHEMA.STATISTICS` |
| `WITH PARSER name` | Fulltext indexes only — pluggable parser |
| `CLUSTERING=YES` | Spider engine only |

Keywords `INDEX` and `KEY` are interchangeable in MariaDB.

## Table Options — MariaDB-Specific or Often-Confused

| Option | Meaning |
|---|---|
| `ENGINE = InnoDB` | Default engine. Set globally via `default_storage_engine`. If an engine isn't installed, substitution happens silently unless `sql_mode` includes `NO_ENGINE_SUBSTITUTION` |
| `ENCRYPTED = YES` | Per-table at-rest encryption. Requires an encryption key-management plugin (e.g. `file_key_management`) and `innodb_encrypt_tables`. Combine with `ENCRYPTION_KEY_ID = N` |
| `PAGE_COMPRESSED = 1` | InnoDB / Aria page-level compression (LZ4 / zstd / lzma / lz4hc / bzip2 / snappy, depending on which compression provider plugins are loaded). **Distinct from `ROW_FORMAT = COMPRESSED`** (older InnoDB zlib mechanism). Add `PAGE_COMPRESSION_LEVEL = 0..9` |
| `ROW_FORMAT = …` | Per-engine row layout. InnoDB: `COMPACT \| DYNAMIC \| COMPRESSED \| REDUNDANT`. Aria: `PAGE \| FIXED \| DYNAMIC`. MyISAM: `FIXED \| DYNAMIC \| COMPRESSED` |
| `SEQUENCE = 1` | Makes this `CREATE TABLE` produce a sequence instead of a regular table. Prefer the dedicated `CREATE SEQUENCE` statement |
| `TRANSACTIONAL = 1` | Crash-safe Aria. Aria-only; ignored on other engines |
| `IETF_QUOTES = YES` | Aria's IETF-style identifier quoting. Aria-only |
| `WITH SYSTEM VERSIONING` | Table-level versioning (see temporal section) |
| `UNION = (t1, t2, …)` | MERGE engine table list |
| `CONNECTION = '…'` | Connection string for FederatedX / CONNECT engines |
| `DATA DIRECTORY = '…'` / `INDEX DIRECTORY = '…'` | Place table data or indexes on a different filesystem. Absolute paths only |

## Atomic DDL

- Plain `CREATE TABLE` is **atomic** since 10.6.1 — crash recovery either has the table fully created or not at all.
- `CREATE OR REPLACE TABLE` is **atomic by default from 13.2**: if the `CREATE` fails or the server crashes, the old table is restored. The old table is still dropped first when `drop_before_create_or_replace` is `ON`, for `TEMPORARY` tables, views, engines with expensive rename (S3), and on replicas. Before 13.2 it is **crash-safe but not atomic**.
- All DDL still triggers an implicit commit; there is no way to roll back a `CREATE TABLE` from inside a transaction.

## See Also

- **`mariadb-system-versioned-tables`** — full system-versioning, application-time, bitemporal, querying history, partitioning, removing history
- **`mariadb-vector`** — `VECTOR(N)` columns and vector indexes
- Canonical reference on `mariadb.com/docs`, consult only for edge cases not covered here: <https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-table>

<sub>_This page is: Copyright © 2026 MariaDB. All rights reserved._</sub>

Wersje

RepozytoriumWynikLicencja
Ta strona · mariadb-corporation/ai-plugins100LGPL
mariadb-corporation/ai-plugins100LGPL

Migawka bazy z 13.11.2026 · wersja paczki 20ee56fdf1fe (skrót SHA-256 całej paczki)

Pobrania w ostatnich 30 dniach: 0 · ranking

Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.

Podobne skille

  • 100mariadb-drop-table
    Opisuje składnię i zachowanie MariaDB przy DROP TABLE: RESTRICT i CASCADE, klucze obce, IF EXISTS i atomową podmianę tabeli.
  • 100mariadb-drop-table
    Opisuje składnię i zachowanie DROP TABLE w MariaDB: RESTRICT i CASCADE jako polecenia bez efektu, DROP TEMPORARY TABLE, częściowe usunięcia, klucze obce, niejawny commit oraz atomowy CREATE OR REPLACE.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.