Skip to content
Tutorials Center
My learning

Topics

Lesson 95 of 125 ยท Database Systems

MySQL and MariaDB Essentials

Learn what makes MySQL and MariaDB different, from backticks, AUTO_INCREMENT and ON DUPLICATE KEY UPDATE to InnoDB, collations and GROUP BY modes.

MySQL is one of the most widely deployed databases in the world. It powers WordPress, countless PHP and Laravel apps, and many large web companies. MariaDB started in 2009 as a fork of MySQL by some of its original developers, and the two still share most of their syntax. If you know the SQL from this course, you can already write most MySQL queries. What trips people up are the small differences: how quotes work, how text is compared, how upserts look, and a few defaults that surprise newcomers.

This lesson walks through those differences. Each MySQL snippet is read-only (it is written for MySQL 8.4 LTS and the 9.x releases), and wherever the same idea exists in SQLite, you get a runnable version next to it so you can see the behavior for yourself.

Connecting and exploring a database

You talk to a MySQL server through a client: the classic mysql command-line tool, MySQL Shell (mysqlsh), or a graphical tool such as MySQL Workbench, DBeaver or DataGrip. MariaDB ships an almost identical client called mariadb.

Terminal
# Connect as user "app" to the database "shop"; -p asks for the password
mysql -u app -p shop

Once connected, a handful of SHOW commands answer the "what is in here?" questions:

SQL
-- MySQL / MariaDB
SHOW DATABASES;
USE shop;                    -- switch the current database
SHOW TABLES;
DESCRIBE products;           -- columns, types, keys, defaults
SHOW CREATE TABLE products;  -- the full CREATE TABLE statement

In MySQL a "database" and a "schema" are the same thing: CREATE SCHEMA is just a synonym for CREATE DATABASE. SQLite has one database per file and answers the same questions with the sqlite_schema table and the table_info pragma:

SQL Inspecting a table in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both systems also support the standard information_schema views in some form. In MySQL, SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'products'; gives a portable answer that works in PostgreSQL and SQL Server too.

Quoting: backticks and double quotes

Standard SQL quotes identifiers (table and column names) with double quotes and strings with single quotes. MySQL breaks that rule by default:

  • Identifiers are quoted with backticks: `order`, `first name`.
  • Double quotes are string literals, just like single quotes. "Berlin" is the text Berlin, not a column.
SQL
-- MySQL (default sql_mode)
SELECT `name`, `price`
FROM `products`
WHERE `name` = "Fountain Pen";   -- "Fountain Pen" is a string here

That query would fail in PostgreSQL, where "Fountain Pen" means a column. You can make MySQL follow the standard by adding ANSI_QUOTES to the sql_mode setting, but most existing MySQL code uses backticks, so learn to read them. The portable habit is simple: use single quotes for every string, and only quote identifiers when you must (for example, a column named order, which is a reserved word).

SQLite is forgiving here. For compatibility, it accepts backticks and even SQL Server's square brackets as identifier quotes:

SQL Backticks work in SQLite too ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQLite has its own double-quote trap. If a double-quoted name does not match any column, SQLite silently treats it as a string. SELECT "nonexistent" FROM products; returns the text nonexistent on every row instead of an error. Always use single quotes for strings so neither database can misread you.

Text comparison and collations

This is the difference that surprises people most. In MySQL 8, the default character set is utf8mb4 (real UTF-8, including emoji) and the default collation is utf8mb4_0900_ai_ci. The suffix matters:

  • ai means accent-insensitive: 'cafe' = 'cafรฉ' is true.
  • ci means case-insensitive: 'berlin' = 'Berlin' is true.

So in MySQL, WHERE city = 'berlin' finds Berlin customers, and a UNIQUE index on email rejects [email protected] if [email protected] already exists. When you need exact comparisons, pick a binary or case-sensitive collation such as utf8mb4_bin or utf8mb4_0900_as_cs for that column.

SQL
-- MySQL
CREATE TABLE tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(50) COLLATE utf8mb4_0900_as_cs NOT NULL UNIQUE
);

Avoid the old utf8 charset. In MySQL, utf8 is an alias for utf8mb3, which stores at most three bytes per character and cannot hold emoji or some rare characters. Use utf8mb4 everywhere.

SQLite works the other way around: = is case-sensitive by default, and you opt in to case-insensitive matching with COLLATE NOCASE (which only folds the ASCII letters Aโ€“Z):

SQL Case-sensitive by default in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query returns no rows, and the second finds the three Berlin customers (Rosa, Rania and Sami). Porting an app from MySQL to another database often breaks searches and logins for exactly this reason. You'll find more about collations in Sorting with ORDER BY and about LIKE case rules in Pattern Matching with LIKE.

Data types you will meet

MySQL has a rich, strict type system. The most common types:

Need MySQL / MariaDB SQLite
Whole numbers INT, BIGINT, SMALLINT, TINYINT INTEGER
Exact money DECIMAL(10, 2) Store cents as INTEGER (or REAL for demos)
Short text VARCHAR(255) TEXT
Long text TEXT, MEDIUMTEXT, LONGTEXT TEXT
True/false BOOLEAN (an alias for TINYINT(1)) INTEGER 0/1
Date and time DATE, DATETIME, TIMESTAMP TEXT in ISO format
Fixed set of values ENUM('pending', 'shipped') TEXT + CHECK (... IN (...))
JSON JSON (binary format in MySQL) TEXT with JSON functions

A few details are worth knowing. TIMESTAMP is stored in UTC and converted to the session time zone, but its range ends in January 2038, so prefer DATETIME for future dates. BOOLEAN is really a tiny integer, so TRUE is just 1. And MySQL's default sql_mode includes STRICT_TRANS_TABLES, which rejects values that don't fit (for example, a 300-character string in VARCHAR(255)) instead of silently truncating them, as very old MySQL versions did. The full comparison lives in Data Types.

AUTO_INCREMENT and the new row's id

MySQL generates ids with the AUTO_INCREMENT column attribute, and you read the id of the row you just inserted with LAST_INSERT_ID():

SQL
-- MySQL
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
) ENGINE = InnoDB;

INSERT INTO categories (name) VALUES ('Garden');
SELECT LAST_INSERT_ID();   -- the id generated in THIS connection

LAST_INSERT_ID() is per connection, so two users inserting at the same moment each get their own id. After a multi-row insert, it returns the id of the first inserted row. MySQL has no RETURNING clause; MariaDB added INSERT ... RETURNING in version 10.5 (and DELETE ... RETURNING long before).

In SQLite, an INTEGER PRIMARY KEY column fills itself in, last_insert_rowid() plays the role of LAST_INSERT_ID(), and RETURNING hands back the new row directly:

SQL Generated ids in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The shop already has six categories, so Garden gets id 7, and the second statement returns Pets (8) and Toys (9). See Auto-Increment and Identity Columns for how every major database does this.

LIMIT, and its comma form

MySQL uses LIMIT like SQLite and PostgreSQL. It also accepts an older two-number form, LIMIT offset, count, where the first number is the offset. That order is easy to misread, so prefer the explicit LIMIT count OFFSET offset.

SQL
-- MySQL: both lines skip 2 rows and return the next 3
SELECT name, price FROM products ORDER BY price DESC LIMIT 2, 3;
SELECT name, price FROM products ORDER BY price DESC LIMIT 3 OFFSET 2;

SQLite understands both forms as well:

SQL Two ways to write the same page ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both return the third, fourth and fifth most expensive products: Adjustable Dumbbells, Studio Headphones and Mechanical Keyboard. For large tables, read about keyset pagination in LIMIT, OFFSET and Pagination.

Upserts: ON DUPLICATE KEY UPDATE

An upsert inserts a row, or updates it if a row with the same key already exists. MySQL's version triggers on any PRIMARY KEY or UNIQUE index conflict:

SQL
-- MySQL 8.0.19+: "AS new" names the row you tried to insert
INSERT INTO inventory (sku, quantity)
VALUES ('MUG-01', 5) AS new
ON DUPLICATE KEY UPDATE quantity = inventory.quantity + new.quantity;

-- Older style, deprecated since 8.0.20 but still common in existing code
INSERT INTO inventory (sku, quantity)
VALUES ('MUG-01', 5)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);

MySQL also has two shortcuts with sharp edges. INSERT IGNORE skips rows that conflict, but it also downgrades many other errors to warnings, so bad data can slip in silently. REPLACE INTO deletes the old row and inserts a new one, which fires delete triggers and cascading deletes, and resets any column you didn't supply.

SQLite (and PostgreSQL) use the standard-flavored ON CONFLICT clause, where excluded refers to the row you tried to insert. SQLite also has INSERT OR IGNORE and REPLACE, which behave like MySQL's:

SQL The SQLite upsert

Press Run (or Ctrl + Enter) to see the result here.

MUG-01 grows from 10 to 15, BAG-03 is inserted with 7, and CAP-02 keeps its 4 because the conflicting insert was ignored. Upserts with ON CONFLICT covers this in depth, including MERGE.

GROUP BY and ONLY_FULL_GROUP_BY

Standard SQL says every column in the SELECT list of a grouped query must be either grouped or aggregated. Old MySQL versions didn't enforce that and returned an arbitrary value from the group instead, which produced subtly wrong reports. Since MySQL 5.7, the default sql_mode includes ONLY_FULL_GROUP_BY, and a query like this fails:

SQL
-- MySQL: error 1055, "name" is neither grouped nor aggregated
SELECT category_id, name, MAX(price)
FROM products
GROUP BY category_id;

MySQL does allow columns that are functionally dependent on the grouped columns: if you group by products.id, the primary key, you may select any other column of products. When you truly don't care which value you get, say so with ANY_VALUE(name).

SQLite is permissive here, but in a documented, useful way. With a single MAX() or MIN(), the "bare" columns come from the row that holds the maximum or minimum:

SQL Bare columns in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Each row shows the most expensive product in its category, such as the Ultralight Tent at 189 in category 2. It's handy, but the query won't run in MySQL with default settings, in PostgreSQL or in SQL Server. The portable version uses a window function, as shown in ROW_NUMBER, RANK and DENSE_RANK.

Storage engines: InnoDB

MySQL has a pluggable storage layer: each table has an engine that decides how rows are stored, locked and recovered. You'll almost always use InnoDB, the default since MySQL 5.5:

  • Full transactions with COMMIT and ROLLBACK, plus crash recovery.
  • Row-level locking and MVCC, so readers don't block writers. The default isolation level is REPEATABLE READ (PostgreSQL and SQL Server default to READ COMMITTED).
  • Foreign keys are enforced.
  • Rows are stored in primary-key order (a clustered index), so a short, ever-increasing primary key keeps inserts fast.

The old MyISAM engine has no transactions, no foreign keys and only table-level locks. You'll still see it in legacy schemas; convert those tables with ALTER TABLE t ENGINE = InnoDB;. One more InnoDB fact matters for migrations: DDL statements such as CREATE TABLE and ALTER TABLE cause an implicit commit, so you can't roll back a schema change the way you can in PostgreSQL or SQLite. More on that in Isolation Levels and Locking and Backups, Migrations and Maintenance.

Strings, dates and JSON

Function names differ more than anything else between dialects. The most important trap: in MySQL, || means logical OR by default (this use has been deprecated since 8.0.17, but it still works), so 'a' || 'b' gives 0, not ab. Use CONCAT():

SQL
-- MySQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       DATE_FORMAT(signed_up, '%Y-%m') AS signup_month,
       DATE_ADD(signed_up, INTERVAL 30 DAY) AS trial_ends,
       DATEDIFF(CURDATE(), signed_up) AS days_since
FROM customers;

SELECT country,
       GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ') AS names
FROM customers
GROUP BY country;

Here are the same ideas in SQLite. Notice that SQLite's ->> operator even accepts MySQL-style '$.path' arguments:

SQL Strings, dates and JSON in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query lists the three German customers, the second gives Carlos, Nico, Olga for Canada and Aiden, Hana, Rosa, Ximena for France, and the last returns red. In MySQL you'd write doc->>'$.color' against a JSON column; see Working with JSON.

What MySQL and MariaDB support (and don't)

Modern MySQL has caught up on most of the SQL you learn in this course, but a few gaps remain:

Feature MySQL 8.4 / 9.x MariaDB 11.x / 12.x SQLite 3.49
CTEs and recursive CTEs Yes (8.0+) Yes Yes
Window functions Yes (8.0+) Yes Yes
INTERSECT / EXCEPT Yes (8.0.31+) Yes Yes
FULL OUTER JOIN No (use UNION of two outer joins) No Yes
RETURNING No INSERT/DELETE/REPLACE Yes
CHECK constraints Enforced (8.0.16+) Enforced Enforced
Upsert syntax ON DUPLICATE KEY UPDATE Same ON CONFLICT
Sequences No CREATE SEQUENCE No
Transactional DDL No (implicit commit) No Yes
WITH ROLLUP Yes Yes No

MariaDB has drifted from MySQL in its own direction. It added sequences, system-versioned (temporal) tables, a native UUID type and, recently, a VECTOR type for AI search, while MySQL 9 added its own VECTOR type. Their JSON support also differs: MySQL stores JSON in a binary format, while MariaDB treats JSON as an alias for LONGTEXT with a validity check. Treat them as close cousins, and test your app on the one you'll actually deploy.

Versions. MySQL now ships Long-Term Support (LTS) releases, such as 8.4, alongside faster "Innovation" releases (9.x) that add features sooner. Production systems usually stay on an LTS. MariaDB also has LTS releases (11.4, 11.8 and 12.3, for example).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In MySQL with default settings, what does WHERE city = "Berlin" compare city with?

    MySQL treats double quotes as string quotes unless ANSI_QUOTES is enabled. Identifiers use backticks.
  2. 2. With MySQL's default collation utf8mb4_0900_ai_ci, is 'berlin' = 'BERLIN' true?

    ci means case-insensitive and ai accent-insensitive. SQLite, by contrast, compares exactly unless you add COLLATE NOCASE.
  3. 3. In SQLite, the inventory table holds ('MUG-01', 10). After INSERT INTO inventory VALUES ('MUG-01', 5) ON CONFLICT (sku) DO UPDATE SET quantity = quantity + excluded.quantity;, what is the quantity?

    excluded.quantity is the 5 you tried to insert, added to the existing 10. MySQL writes the same idea with ON DUPLICATE KEY UPDATE.
  4. 4. Why should new MySQL tables use InnoDB rather than MyISAM?

    InnoDB is the default engine and provides crash-safe transactions, row-level locking and enforced foreign keys. MyISAM has none of these.
  5. 5. What does SELECT 'a' || 'b'; return in MySQL with the default sql_mode?

    In MySQL, || is a (deprecated) synonym for OR unless PIPES_AS_CONCAT is set. Use CONCAT('a', 'b').

Summary

  • MySQL and MariaDB share most syntax; MariaDB adds sequences, RETURNING and temporal tables, MySQL 9 adds features through Innovation releases next to the 8.4 LTS.
  • Identifiers use backticks; double quotes are strings by default. Use single quotes for strings everywhere.
  • The default utf8mb4_0900_ai_ci collation makes text comparisons case- and accent-insensitive, unlike SQLite.
  • AUTO_INCREMENT + LAST_INSERT_ID() replace SQLite's INTEGER PRIMARY KEY + last_insert_rowid(); MySQL has no RETURNING.
  • Upserts use INSERT ... AS new ON DUPLICATE KEY UPDATE; be careful with INSERT IGNORE and REPLACE.
  • ONLY_FULL_GROUP_BY rejects ungrouped columns; use ANY_VALUE() or a window function.
  • Use InnoDB for transactions, row locking and foreign keys; DDL causes an implicit commit.
  • Use CONCAT() instead of ||, and expect different date function names.

Next: SQL Server and Oracle Essentials, where you'll meet T-SQL, TOP, IDENTITY, MERGE, sequences and DUAL.