Тип SET в MySQL: связка с PHP для сложных запросов

Расширенные примеры работы с SET в PHP и MySQL

Ниже представлены более детальные и нестандартные сценарии использования поля SET с примерами кода и результатами.

1. Вставка данных из массива PHP с проверкой допустимых значений

Часто приходит массив выбранных опций. Необходимо отфильтровать недопустимые значения и преобразовать в строку.

<?php
// Допустимые варианты для поля 'extras' SET('gps','abs','sunroof')
$allowed = ['gps', 'abs', 'sunroof'];
$input = ['abs', 'gps', 'bluetooth']; // bluetooth недопустим
$safe = array_intersect($input, $allowed);
$value = implode(',', $safe); // 'abs,gps'

$stmt = $pdo->prepare('INSERT INTO cars (name, extras) VALUES (:name, :extras)');
$stmt->execute([':name' => 'Tesla', ':extras' => $value]);
?>

INSERT успешен, в поле записано 'abs,gps'

2. Выборка всех возможных значений SET из информационной схемы MySQL

Иногда нужно динамически получить список допустимых элементов для поля SET, чтобы построить форму или валидатор.

<?php
$table = 'cars';
$field = 'extras';
$stmt = $pdo->prepare("SHOW COLUMNS FROM $table LIKE :field");
$stmt->execute([':field' => $field]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// В $row['Type'] содержится что-то вроде "set('gps','abs','sunroof')"
preg_match("/^set\('(.+)'\)$/", $row['Type'], $matches);
$options = explode("','", $matches[1]);
print_r($options);
?>

Array
(
    [0] => gps
    [1] => abs
    [2] => sunroof
)

3. Обновление SET: добавление нового значения без удаления старых

Используем CONCAT_WS, но надо избежать дублирования. Лучше сделать на PHP, но в SQL можно так:

UPDATE user_roles 
SET roles = CONCAT_WS(',', roles, 'publisher')
WHERE id = 1 AND NOT FIND_IN_SET('publisher', roles);

Условие NOT FIND_IN_SET предотвращает повторное добавление. После обновления значение может выглядеть как 'admin,editor,publisher'.

4. Удаление определённого значения из SET (SQL способ)

UPDATE user_roles
SET roles = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', roles, ','), ',editor,', ','))
WHERE FIND_IN_SET('editor', roles);

Этот запрос заключает текущее значение в запятые, заменяет ',editor,' на ',' и удаляет лишние запятые по краям. Работает, но громоздко. На PHP операция выполняется проще и надёжнее.

5. Использование битовых операций для выборки всех записей, содержащих комбинацию флагов

Если поле SET определено как `flags SET('read','write','execute')` и известно, что 'read' = 1, 'write' = 2, 'execute' = 4, то выбрать записи с правами на запись и выполнение можно так:

SELECT name, flags 
FROM permissions 
WHERE (flags + 0) & 2 AND (flags + 0) & 4;

В PHP такой запрос можно параметризовать, передавая битовые маски как целые числа:

<?php
$writeBit = 2;
$executeBit = 4;
$stmt = $pdo->prepare('SELECT name, flags FROM permissions WHERE (flags + 0) & :write AND (flags + 0) & :execute');
$stmt->execute([':write' => $writeBit, ':execute' => $executeBit]);
?>

6. Преобразование строки SET в массив с учётом пустого значения

<?php
$roles = $row['roles'] ?? ''; // если NULL, то пустая строка
$rolesArray = $roles === '' ? [] : explode(',', $roles);
?>

Важно обработать случай пустого SET, чтобы не получить массив с одной пустой строкой.

7. Сравнение производительности: FIND_IN_SET vs битовые маски

Для демонстрации разницы можно выполнить EXPLAIN на больших таблицах. Битовые операции обычно не используют индекс, но быстрее, чем строковые функции. Однако для массовой фильтрации всё же рекомендуется нормализация.

EXPLAIN SELECT * FROM user_roles WHERE FIND_IN_SET('admin', roles);
EXPLAIN SELECT * FROM user_roles WHERE (roles + 0) & 1;  -- если admin первый в SET

8. Обработка SET в рамках транзакции с блокировками

<?php
$pdo->beginTransaction();
try {
    // Читаем текущее значение
    $stmt = $pdo->prepare('SELECT roles FROM user_roles WHERE id = ? FOR UPDATE');
    $stmt->execute([$id]);
    $row = $stmt->fetch();
    $rolesArray = explode(',', $row['roles']);
    // Добавляем новую роль, если её нет
    if (!in_array('manager', $rolesArray)) {
        $rolesArray[] = 'manager';
        $newRoles = implode(',', $rolesArray);
        $stmtUpdate = $pdo->prepare('UPDATE user_roles SET roles = ? WHERE id = ?');
        $stmtUpdate->execute([$newRoles, $id]);
    }
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}
?>

Такой подход безопасен при конкурентном доступе.