List of MySQL Basic Use Cases

mysql.md

List of MySQL Basic Use Cases

BULK UPDATE

When updating multiple rows with different values it is much quicker to use a bulk update.

UPDATE people 
SET name = 
  (CASE 
    WHEN id = 1 THEN 'Karl'
    WHEN id = 2 THEN 'Tom'
    WHEN id = 3 THEN 'Mary'
   END)
WHERE id IN (1, 2, 3);

By bulk updating only one query can be sent to the server instead of one query for each row to update. The cases should contain all possible parameters looked up in the WHERE clause.

---

ROW NUMBERS

When selecting row number, you can do:

SELECT 
  row_number() OVER (ORDER BY id) AS rowNumber, id 
FROM users; 

---

EXTRACT JSON KEY WITH PATH EXPRESSION ERROR

SELECT JSON_EXTRACT(fieldnames, '$."BAR-BAZ"') from foo;

---

UPDATE JOIN

UPDATE foo
join bar
ON foo.colA = bar.colA
SET foo.colB = bar.colB

---

COUNT TOTAL ROWS IN A GROUP BY STATEMENT

SELECT 
  COUNT(*) OVER () AS total
FROM foo
  GROUP BY foo.bar, foo.baz

---

MYSQL SET DEFAULT VALUE TO A JSON TYPE COLUMN

ALTER TABLE templates CHANGE [col] [col] JSON NOT NULL DEFAULT (JSON_OBJECT());
ALTER TABLE templates CHANGE [col] [col] JSON NOT NULL DEFAULT (JSON_ARRAY());

search-mysql-tables.md

Search MYSQL Tables

Search tables from your database

Code

SELECT *
FROM information_schema.columns
WHERE column_name LIKE '%search_query%'
AND table_schema = 'table_name'