Опубликовано

Mysql: Illegal mix of collations

Получил дамп для развертки приложения, при тестировании постоянно падало с ошибкой PDO: Illegal mix of collations, оказалось что один из запросов делал джойн таблицы с разными collation (сопоставление), хотя на тесте и проде таблицы имели одинаковое сопоставление

В итоге нужно было привести все к одному командой:

1
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

 

Опубликовано

Визуальное объяснение SQL JOIN

В этой статье мы рассмотрим визуальное представление соединение таблиц SQL командой JOIN.

Очень часто не хватает визуального представления, для понимания как происходит соединенная выборка данных в таблицах с использованием условий:

В качестве визуализации будем использовать диаграммы Эйлера

Для начала нам потребуется две таблицы с некоторыми перекликающимся записями:

1
2
3
4
5
6
7
Table A       Table B
id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

INNER JOIN

Рассмотрим классический выбор данных которые присутствуют в обеих таблицах

1
2
3
4
5
6
7
8
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name
 
id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja

Внутреннее соединение INNER JOIN производит выборку только записей, которые соответствуют как в таблице А, так и в таблице В.

FULL OUTER JOIN

1
2
3
4
5
6
7
8
9
10
11
12
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
 
id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga      
null  null       3     Darth Vader

Полное внешнее объединение FULL OUTER JOIN производит выборку множество всех записей в таблице А и в таблице B, с соответствующими записями с обеих сторон при их наличии. Если совпадения нет, отсутствует сторона будет содержать NULL.

LEFT OUTER JOIN

1
2
3
4
5
6
7
8
9
10
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
 
id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

Левое внешнее объединение LEFT OUTER JOIN производит полный набор записей из таблицы А, с соответствующей записи (если таковые имеются) к Таблице B. Если совпадения нет, то правая часть будет содержать NULL.

LEFT OUTER JOIN с исключением записей

1
2
3
4
5
6
7
8
9
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null
 
id  name       id     name
--  ----       --     ----
2   Monkey     null   null
4   Spaghetti  null   null

Для получения набора записей только в таблице А, но не в таблице B, мы выполняем тоже самое левое внешнее соединение LEFT OUTER JOIN, но исключаем записи, оставляя только случай в котором нету сопоставимых записей левой стороне. В итоге справа у нас только NULL.

FULL OUTER JOIN с выбором уникальных записей

1
2
3
4
5
6
7
8
9
10
11
12
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null
 
id    name       id    name
--    ----       --    ----
2     Monkey     null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

Для получения уникального набора записей в таблице A и в таблице B, мы выполняем полное внешнее соединение FULL OUTER JOIN, исключая отсутствующие записи при полной выборке с обеих сторон.

CROSS JOIN

Так же существует выборка перекрестного соединения, CROSS JOIN, с перебором все вариантов, которое не объяснить данными диаграммами

1
2
SELECT * FROM TableA
CROSS JOIN TableB

Данное перекрестное соединение выбирает буквально «все к всему», в результате мы получим 4 x 4 = 16 записей, намного более чем в оригинале мы имеем в таблицах.

Опубликовано

Как загрузить дамп MySQL базы через консоль

Залить базу через mysql-клиент (mysql) через ssh или консоль можно следующими командами:

1
mysql -uИМЯ_ПОЛЬЗОВАТЕЛЯ -pПАРОЛЬ ИМЯ_БАЗЫ < dump.sql

Если вы работаете на локальной машине и у вас windows, для использования команды mysql вам нужно прописать путь к mysql в path (пути разделяются точкой с запятой ;):

bin

 

Если в качестве сервера вы используйте OpenServer, вам достаточно запустить встроенную консоль:

1430474796387