Firebase 4.0 sandbox

Сегодня (с небольшим отставанием) добавлена поддержка Firebird 4 в песочнице SQLize.online

Для выполненя запроса просто наберите или вставьте его в левой панели и нажмите "Run SQL code". В случае ошибки, можно попросить о помощи ChatGPT прямо из песочницы!

Недостойное поведение Oracle DB

Сегодня база данных Oracle, была уличена в недостойном поведении, а именно в неявном преобразовании пустых строк к NULL

Посмотрите сами и убедитесь:

create table null_test (
    id int,
    test varchar(64)
);

insert into null_test values (1, 'Test');
insert into null_test values (2, null);
insert into null_test values (3, '');

select id, test, case when test is null then 'test is NULL' else 'test NOT NULL' end isNULL
from null_test;


select id, test
from null_test
where test = '';

Результат:

+====+========+===============+
| ID | TEST   | ISNULL        |
+====+========+===============+
| 1  | Test   | test NOT NULL |
| 2  | (null) | test is NULL  |
| 3  | (null) | test is NULL  |
+----+--------+---------------+

+====+======+
| ID | TEST |
+====+======+

Даже SQLITE известная своим пренебрежением к типам данных, не позволяет себе таких вольностей.

К сожалению, единственное что я могу сделать, это опубликовать этот пост чтобы предостеречь Вас от возможных ошибок.

Невидимые столбцы в MySQL 8

Продолжая писать о малоизвестных возможностях MySQL хочу рассказать о портированном со старшего брата Oracle DB. Так начиная с версии 8.0.23 любой столбец таблицы можно объявить невидимым.

Это можно сделать как при создании таблицы, так и при добавлении новой колонки.

CREATE TABLE test_table (
  a INT,
  b DATE INVISIBLE
) ENGINE = InnoDB;
ALTER TABLE test_table ADD COLUMN c INT INVISIBLE;

Видимость колонки может быть изменена в любое время:

ALTER TABLE test_table CHANGE COLUMN b b DATE VISIBLE;
ALTER TABLE test_table MODIFY COLUMN b DATE INVISIBLE;
ALTER TABLE test_table ALTER COLUMN c SET VISIBLE;

Следующие команды покажут скрытые колонки с флагом INVISIBLE:

SHOW COLUMNS FROM test_table;
SHOW CREATE TABLE test_table;

При выборке всех столбцов (SELECT *) скрытые столбцы не видны, только при явном указании:

TABLE test_table; SELECT * FROM test_table; -- столбец с скрыт

SELECT a, b, c FROM test_table; -- столбец с показан

То же самое при вставке, без явного указания не работает:

INSERT INTO test_table VALUES (1, now(), 33); == ошибка
INSERT INTO test_table () VALUES (1, now(), 33); == тоже ошибка

INSERT INTO test_table VALUES (1, 22); -- вставлен NULL
INSERT INTO test_table (a, b, c) VALUES (1, now(), 33); -- все значения

UPDATE для скрытых столбцов работает как обычно.

Зачем нужно? Безопасность данных (скрыть чувствительные данные). Знаете еще применения - пишите в комментах

Официальная документация

Сайт для тестирования SQL

Элегантное решение проблемы увеличения инкремента при вставке дубликатов.

Множество раз в различных форумах всплывает вопрос: Как избежать увеличения автоинкремента при вставке дубликатов в таблицу?

Хотя я сам на тех же форумах отвечал что это не критично и не стоит из-за этого волноваться, а если волнуетесь то используйте bigserial и тогда точно хватит! Но душа тем не менее требовала гармонии. И так классика:

create table tbl (
    id serial, 
    val text,
    primary key (id),
    unique (val)
);
-- Вставляем Раз и Два
insert into tbl (val) values ('One'), ('Two');

-- Попробуем так
insert into tbl (val) values ('One'), ('Three') on conflict (val) do nothing;

select * from tbl;

+====+=======+
| id | val   |
+====+=======+
| 1  | One   |
| 2  | Two   |
| 4  | Three |
+----+-------+

Видно, автоинкремент растет даже если мы используем UPSERT Конечно есть вариант с проверкой NOT EXISTS, но это не для слабонервных:

insert into target2 (val) 
select val from source
where not exists (select true from target2 where target2.val = source.val);

И вот наконец то в PostgreSQL 15 завозят оператор MERGE:

merge into target3 t
using source s on t.val = s.val
when not matched then insert (val) values (val);

здесь тестируем SQL запросы

здесь читаем про Применение оператора MERGE в PostgreSQL

Компания Oracle выпустила новую версию своей базы данных Oracle 23c

Среди нововведений:

• SELECT выражений без указания раздражающего FROM DUAL

select
  'Hello world'     as txt,
   7 * 6            as nmbr,
   sysdate          as sdate;

• поддержка типа boolean (очень надеюсь что в ближайшее его завезут и в MySQL):

select true, false;

create table bool_test (
	val varchar2(10), 
	flg boolean
);

insert into bool_test values ('true'  , true );
insert into bool_test values ('false' , false);
insert into bool_test values ('null'  , null );
insert into bool_test values ('t'     ,'t'   );
insert into bool_test values ('yes'   ,'yes' );
insert into bool_test values ('True'  ,'True');
insert into bool_test values ('f'     ,'f'   );
insert into bool_test values ('0'     , 0    );

обратите внимание что строки 'y', 'yes', 't', 'true' неявно конвертируются в True, а значения 'n', 'no', 'f', 'false' в False в не зависимости от регистра и начальных/конечных пробелов. Число 0 сохраняется как False остальные любые числа как True

Поле типа boolean может быть использовано в where:

select * from bool_test where     flg;
select * from bool_test where not flg;

Вместе с новым типом данных введена новая функция to_boolean()

Некоторые плюшки из MySQL перенесены в Oracle. Например

• DROP/CREATE TABLE IF [NOT] EXISTS

drop   table IF     EXISTS my_test;
create table IF NOT EXISTS my_test (num number, txt varchar2(10));

• GROUP BY on expression aliases/position number

Все новые уже функции доступны на SQLize.online

Oracle выпустила новую версию своей СУБД – Oracle Database 23c.

Oracle Database 23c предлагает новые функции, которые помогут разработчикам создавать новое поколение критически важных приложений с высокой производительностью.

В качестве единственной полной и простой совмещенной базы данных для разработчиков, инженеров данных и администраторов баз данных, Oracle Database 23c включает JSON Relational Duality, хранимые процедуры на JavaScript, анализ графовой структуры операционных данных, автоматизированную обработку распределенных транзакций микросервисов (известных как sagas), улучшенные автоматические материализованные представления, управление SQL-планами в режиме реального времени, True Cache, ML-усиленное предсказание статистики данных для оптимизации выполнения SQL-запросов и нативная репликация фрагментов баз данных.

Дополнительным функционалом является возможность запуска приложений Kafka непосредственно в Oracle Database и защита от несанкционированного SQL через любой путь выполнения с помощью нового встроенного SQL-брандмауэра в Oracle Database. Он доступен в бета-версии по всему миру для клиентов Oracle, которые завершат процесс регистрации в бета-версии.

На SQLize.online мы уже обновились до Oracle 23c, поэтому нашим пользователям доступны все новые возможности для работы с базами данных Oracle. Теперь вы можете использовать новые функции и инструменты прямо в нашем онлайн-редакторе SQL и наслаждаться более быстрой и удобной работой с данными.

На днях натолкнулся на дикий баг в мускуле

Оказывается что при использовании короткого синтаксиса внешний ключ не создается

create table test (
 id int primary key,
 first_name varchar(20),
 last_name varchar(30)
);

create table ref1 (
 id int primary key,
 test_id int references test(id)
);

В этом примере внешний ключ для поля `test_id` не создаётся

Подробное описание здесь: https://tproger.ru/articles/staryj-bag-v-mysql-istoriya-detektiva/

PHP & SQL песочница - сейчас с ChatGPT

Здравствуйте ужважаемые лентяи. Теперь Вы можете еще больше времени предаваться лени так как мой сайт PHPize.online научился исправлять ошибки в коде PHP при помощи ИИ.

Так вот, если при исполнении Вашего кода произойдет ошибка - Вы увидите красивую зелёную кнопку. Нажмите её и ИИ постарается найти решение за Вас.

 Happy Coding!

SQL Server 2022 - GENERATE_SERIES

Конструкция GENERATE_SERIES - это новая функция в T-SQL 2022, которая может использоваться для генерации последовательностей значений в запросах. Эта функция подобна аналогичной функции в других языках программирования, например, в PostgreSQL.

Одной из основных применений функции GENERATE_SERIES в T-SQL является генерация последовательности чисел. Вот пример использования этой функции для генерации последовательности чисел от 1 до 10:

SELECT value FROM GENERATE_SERIES(1, 10)

Этот запрос вернет следующий результат:

value
1
2
3
4
5
6
7
8
9
10

Функция GENERATE_SERIES может принимать два или три аргумента: начальное значение, конечное значение и опциональный шаг. В приведенном выше примере начальным значением было 1, конечным значением - 10, а шаг был опущен, поэтому значения генерировались с шагом 1.

Функция GENERATE_SERIES также может использоваться для генерации последовательностей букв и даже дат. Вот пример использования этой функции для генерации последовательности букв от 'A' до 'Z' и дат между '2022-01-01' и '2022-01-10'

SELECT
    CHAR(value + 64) AS letter 
FROM GENERATE_SERIES(1, 26);

SELECT 
    DATEADD(day, number-1, '2022-01-01') AS Date
FROM GENERATE_SERIES(1, DATEDIFF(day, '2022-01-01', '2022-01-10') + 1)

Вы можете протестировать эти у другие запросы на сайте SQLize.online

FETCH FIRST … ROWS WITH TIES

В этой статье я хочу показать новще решение распостраненной задачи. Найти в таблице записи с максимальным значением в одной из колонок.Для примера мы будем искать сотрудников с максимальной зарплатой.

Первое что приходит в голову - это ORDER BY salary DESC LIMIT 1 Логично но не правильно потому что максимальную зарплату мщгут получать несколько сотрудников.

Стандартное решение - сравнить зарплату с результатом подзапроса:

select * 
from employees
where salary = (select max(salary) from employees);

стандартно - не значит эффективно. Если наша СУБД поддерживает оконные функции:

with ranked as (
    select 
        employees.*,
        dense_rank() over (order by salary desc) r
    from employees
) select * from ranked where r = 1;

немного эффективней, но слишком многословно :(

Есть еще один вариант о котором знают не все. Стандарт SQL:2008 предлагает новое решение данной задачи: FETCH FIRST … ROWS WITH TIES :

select * 
from employees
order by salary desc
fetch first 1 rows with ties;

Компактно и элегантно. Проверить эти у другие запросы можно на SQLize.online

MySQL 8: short syntax for select all

Начиная с MySQL 8, вы можете использовать довольно короткий запрос для выбора всех данных из таблицы.
Просто используйте table a; вместо SELECT * FROM a; и получить тот же результат:

mysql> table a;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
+---+---+

mysql> table b;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 1 | 3 |
| 3 | 4 |
+---+---+

mysql> table c;
+===+===+
| m | n |
+===+===+
| 1 | 3 |
| 1 | 3 |
| 3 | 4 |
+---+---+

Этот синтаксис также можно использовать с оператором UNION и INTERSECT

mysql> table a union all table b union all table c;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 1 | 2 |
| 1 | 3 |
| 3 | 4 |
| 1 | 3 |
| 1 | 3 |
| 3 | 4 |
+---+---+

mysql> table a intersect table b;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 3 | 4 |
+---+---+

Вы можете проверить этот код на SQLize.online

Новое в PHP 8.2: mysqli_execute_query / mysqli::execute_query

Начиная с версии PHP 8.2.0 появляется новая функция mysqli_execute_query или mysqli::execute_query если Вы предпочитаете объектно-ориентированный стиль.
Данная функция позволяет убить двух (трёх) зайцев сразу:

• сгенерировать подготовленное выражение

• выполнить его подставив значения из массива переменных

• получить результат в виде ассоциативного массива

Давайте попробуем это на практике:

<?php
$query = 'SELECT Name FROM City WHERE District=? ORDER BY Name LIMIT 5';

/* здесь происходит магия */
$result = $mysqli->execute_query($query, ['Nordrhein-Westfalen']);
/* здесь происходит магия */

foreach ($result as $row) {
    printf("%s \n", $row["Name"]);
}

Вы можете проверить этот код на PHPize.online