SQL · Theory

8.0

Theory

375/439

8.0

MySQL 8 is the current major line: CTE (WITH), window functions, roles, better UTF8 (utf8mb4 default), new default authentication (caching_sha2_password — old PHP clients sometimes fail until you fix the plugin or upgrade the client).

If a connector error mentions caching_sha2, it is a client compatibility issue, not ‘MySQL is broken’.

Trap — installing 8 and using a 2014 JDBC/PHP driver without reading the error.

On the example next to this theory: 8.0: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

8.0: WITH, ROW_NUMBER(), utf8mb4. Watch old connectors.

8.0 — sample query

-- 8.0
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

8.0: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefMySQL 8 = CTE, windows, utf8mb4, new password plugin.
RuleUpgrade clients for caching_sha2_password.
TrapOld drivers vs 8.0 auth.

SQL · Theory

8.0

← All stacks

Theory

375/439

8.0

If a connector error mentions caching_sha2, it is a client compatibility issue, not ‘MySQL is broken’.

Trap — installing 8 and using a 2014 JDBC/PHP driver without reading the error.

On the example next to this theory: 8.0: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

8.0: WITH, ROW_NUMBER(), utf8mb4. Watch old connectors.

8.0 — sample query

-- 8.0
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

8.0: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefMySQL 8 = CTE, windows, utf8mb4, new password plugin.
RuleUpgrade clients for caching_sha2_password.
TrapOld drivers vs 8.0 auth.

8.0

8.0 — sample query

Short notes

Questions

8.0

8.0 — sample query

Short notes

Questions