Как использовать поля JSON в MySQL

MySQL

После MySQL 5.7.8 json изначально поддерживается. В базе данных nosql, такой как mongodb, для хранения данных очень естественно использовать json. Разумное использование json в mysql может принести большое удобство.

Сценарии использования полей Json

Читая примеры руководств по laravel, мы часто видим$user->is_adminЧтобы определить является ли пользователь администратором, но в таблице пользователей admin обычно занимает лишь малую часть.Очень ненужно открывать поле is_admin.В БД будет много бессмысленного хранилища данных,мы можем его использовать для пользователя Таблица создает поле json для хранения нашего поля is_admin

[
    {
        id: 1,
        username: 'weiwenhao',
        rest: { // 冗余字段
            is_admin: 1
        }
    },
    {
        id: 2,
        username: 'eienao',
        rest: null
    }
]

Конечно, даже если мы не используем json, мы не будем использовать is_admin, чтобы судить, является ли он администратором.

Администратора можно отметить, добавив таблицу администратора или RABC.

Это по-прежнему таблица пользователей.Очень распространенное требование — сторонний вход в систему.Если мы добавим его непосредственно в таблицу пользователей.facebook_id,facebook_email,facebook_phone_number,google_id,....поля, которые могут привести к большому количеству бессмысленных данных (даже если они не занимают память или не влияют на производительность)

Одним из решений является использование отношения «один ко многим», которое заключается в создании сторонней таблицы входа в систему для хранения идентификатора/электронной почты/номера телефона стороннего входа в систему и т. д.

Но я предпочитаю использовать поля json для решения этой проблемы.

[
   {
        id: 1,
        username: 'weiwenhao',
        rest: {
            is_admin: 1,
            facebook_id: 2348234,
            facebook_phone_number: 2834723234,
        }
    },
    {
        id: 2,
        username: 'eienao',
        rest: {
            google_id: 2348234,
            google_email: xxx@gmail.com
        }
    }
]

Видно, что использование полей json делает оформление таблицы данных более естественным и централизованным, а бизнес соответственно проще и удобнее.

Использование полей Json в laravel

Во-первых, это файл миграции$table->json('rest')->nullable();

Laravel в определенной степени оптимизировал использование json, для обновления и создания мы можем.

$user = new User;
$user->{'rest->google_id'} = 'xxx'; 
# 如果你的rest字段为null,那么上面的操作会使 null 会变成 {google_id: "xxx"}, 不需要再做 是否为null的判定啦
# 如果仅使用上面的插入操作,也不需要在使用模型的修改器来吧 json => array, array => json啦

Когда значение поля rest равно null, пакетная операция не может быть выполнена, подобноupdate(['rest->google_id' => 'xxx'])Такая операция недействительна, поэтому вышеописанный метод более рекомендуется для выполнения операции обновления.

Удобно использовать для поисковой операции

User::where('rest->google_id','xxx')->firstOrFail()

Что касается эффективности поиска, решение дается в следующем содержании.

Сгенерированный столбец

5.7 Добавлен сгенерированный столбец.Значение сгенерированного столбца вычисляется в соответствии с выражением, содержащимся в определении столбца.Официальный пример: вычислить длину гипотенузы прямоугольного треугольника.

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) # AS (expression) 为生成列的核心语法
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

# 对于上面的插入,查询可以得到如下结果

mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+

Значение sidec выше рассчитывается на основе sidea и sideb и фактически не сохраняется на диске.До mysql5.7 мы могли написать операторы sql, подобные этому, для достижения вышеуказанных требований.

SELECT *,(SQRT(sidea * sidea + sideb * sideb)) as sidec FROM triangle;

Выше приведена основная функция генерируемого столбца.На самом деле генерируемый столбец имеет два подтипа.Приведенный выше пример относится к виртуальному (виртуальному) типу генерируемого столбца, который фактически не хранит значение сайдека на диске.

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

#...
sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) STORED # stored不指定则默认为 virtual
#...

Когда строка создается или обновляется, sidec пересчитывается и сохраняется на диске.

Еще одной важной особенностью сгенерированных столбцов является то, что их можноГенерирует результат оценки выражения столбца для построения индекса, Способ индексации такой же, как и для обычных полей.1

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) # AS (expression) 为生成列的核心语法
  INDEX(`sidec`)
);

Сам индекс также является реальным веществом, хранящимся на диске, поэтому виртуальный генерирует столбец + индекс, что позволяет добиться наиболее эффективного использования дискового пространства.

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

Добавить индекс в поле в json, используя сгенерированный столбец

Возьмите в качестве примера rest.google_id пользовательской таблицы, создайте таблицу

#...
`rest` json NULL,
# JSON_EXTRACT(`rest`,'$.google_id') 等价于 `rest`->'$.google_id'
# 5.7.13版本后 
# JSON_UNQUOTE(JSON_EXTRACT(`rest`,'$.google_id')) 等价于 `rest`->>'$.google_id'
# 使用生成列为json添加索引时,请务必使用 JSON_UNQUOTE(JSON_EXTRACT(`rest`,'$.google_id'))/->>
`google_id` varchar GENERATED ALWAYS AS (`rest`->>'$.google_id')) NULL
UNIQUE INDEX(`google_id`)
#...

в файле миграции laravel

$table->json('rest')->nullable();

$table->string('rest')->nullable()->unique()->virtualAs('`oauth`->>"$.google_id"');

С индексом, когда мы выполняем операцию запроса

select * from users where `rest`->'$.google_id' = 'xxx' # 通常使用这种更加简单的形式
select * from users where `rest`->>'$.google_id' = 'xxx'

# 上面两种表达式会被mysql的优化器在查询阶段自动优化为 select * from users where google_id = 'xxx'

некоторые дополнения

  • virtualAs(oauth->"$.google_id"');Использование символа **->** для создания сгенерированного столбца приведет к тому, что индекс не будет использоваться.Причина не очень ясна.Нужно продолжить изучение руководства.Кроме того, для оператора созданияGENERATED ALWAYSэффект не очень ясен.

  • Что касается null, часто можно увидеть, что использование null в качестве значения поля по умолчанию в mysql приведет к сбою индекса. Но после запроса я обнаружил, что это старая теория китайской медицины. Я предпочитаю использовать null как значение по умолчанию, вместо ''/0/0.0, я думаю, что null более выразительно, и laravel всегда представляет эту идею.

  • Что касается использования json, то в последних проектах в большинстве моих основных таблиц есть поле json для хранения и избыточности некоторых неосновных данных.

Категории