Name: Database Mariadb 10 6
Author: chrishuffman5

搜索技能.../

Database Mariadb 10 6 | Skills Pool

SELECT jt.*
FROM json_data,
     JSON_TABLE(json_col, '$[*]' COLUMNS (
         id INT PATH '$.id',
         name VARCHAR(100) PATH '$.name',
         status VARCHAR(20) PATH '$.status' DEFAULT '"unknown"' ON EMPTY
     )) AS jt
WHERE jt.status = 'active';

-- Standard SQL syntax (10.6+)
SELECT * FROM products ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Equivalent to the MySQL/MariaDB LIMIT syntax
SELECT * FROM products ORDER BY name LIMIT 10 OFFSET 20;

-- Skip rows that are already locked by another transaction
SELECT * FROM job_queue WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Fail immediately if any row is locked
SELECT * FROM accounts WHERE id = 42 FOR UPDATE NOWAIT;

-- Top queries by total latency
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

-- Tables with most I/O
SELECT * FROM sys.io_global_by_file_by_latency;

-- Current sessions and their state
SELECT * FROM sys.session;

-- Unused indexes
SELECT * FROM sys.schema_unused_indexes;

SET SQL_MODE=ORACLE;

-- ROWNUM usage
SELECT * FROM employees WHERE ROWNUM <= 10;

-- Date functions
SELECT ADD_MONTHS(SYSDATE, 3);
SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') FROM employees;

-- On MySQL 8.0 BEFORE migration: switch users to mysql_native_password
ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

-- On MariaDB: use ed25519 (preferred) or mysql_native_password
CREATE USER 'app_user'@'%' IDENTIFIED VIA ed25519 USING PASSWORD('password');

-- This MySQL syntax does NOT work in MariaDB:
-- CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE InnoDB;
-- CREATE TABLE t1 (...) TABLESPACE ts1;

-- MariaDB uses file-per-table by default (innodb_file_per_table=ON)
-- Or the system tablespace

Database Mariadb 10 6

MariaDB 10.6 LTS Version Expert

Identity and Scope

Key Features

Atomic DDL

Database Mariadb 10 6

MariaDB 10.6 LTS Version Expert

Identity and Scope

Key Features

Atomic DDL

JSON_TABLE

SELECT ... OFFSET ... FETCH

SELECT ... SKIP LOCKED / NOWAIT

sys Schema

Oracle Compatibility Enhancements

InnoDB Changes

MySQL 8.0 Migration Gotchas

JSON Format Difference

SHA-256 Authentication Incompatibility

GTID Incompatibility

No CREATE TABLESPACE

Other Differences

Upgrading from 10.6

To MariaDB 10.11 LTS

To MariaDB 11.4 LTS

Version Boundaries

Database Migrations

Database Migrations

Postgres Patterns

Frontend Query & Mutation

Db Migrations

Drizzle