47 фотографий сделают вас продвинутым в MySQL! ! !

задняя часть MySQL

Мы в основном представили основные команды SQL, типы данных и функции в записи MySQL.После того, как вы получите вышеуказанные знания, вы можете начать разработку MySQL, но если вы хотите стать квалифицированным разработчиком, вам нужно иметь некоторые дополнительные навыки.Расширенные навыки , давайте обсудим, какие продвинутые навыки нужны MySQL

Механизм хранения MySQL

Обзор механизма хранения

Основной задачей базы данных является хранение данных, а хранение данных не может обойтись без дисков. Итак, каким образом хранятся данные и как их хранить, это ключ к хранению. Таким образом, механизм хранения эквивалентен механизму хранения данных для хранения данных на уровне диска.

Архитектуру MySQL можно понять в соответствии с трехуровневой моделью.

Механизм хранения также является компонентом MySQL.Это своего рода программное обеспечение.Функции, которые он может выполнять и поддерживать, в основном включают:

  • параллелизм
  • Поддержка транзакций
  • ограничения целостности
  • физическое хранилище
  • поддержка индекса
  • справка по производительности

MySQL по умолчанию поддерживает различные механизмы хранения, которые подходят для различных приложений баз данных. Пользователи могут выбрать подходящий механизм хранения в соответствии со своими потребностями. Ниже приведены механизмы хранения, поддерживаемые MySQL.

  • MyISAM
  • InnoDB
  • BDB
  • MEMORY
  • MERGE
  • EXAMPLE
  • NDB Cluster
  • ARCHIVE
  • CSV
  • BLACKHOLE
  • FEDERATED

По умолчанию, если вы создаете таблицу без указания механизма хранения, будет использоваться механизм хранения по умолчанию.Если вы хотите изменить механизм хранения по умолчанию, вы можете указать его в файле параметров.default-table-type, чтобы иметь возможность просматривать текущий механизм хранения

show variables like 'table_type';

Странно, почему бы и нет? Проверьте онлайн, этот параметр был отменен в 5.5.3

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

show engines \g

При создании новой таблицы вы можете добавитьENGINEКлючевое слово устанавливает механизм хранения только что созданной таблицы.

create table cxuan002(id int(10),name varchar(20)) engine = MyISAM;

Выше мы указалиMyISAMнакопительный двигатель.

Что делать, если вы не знаете механизм хранения таблицы? ты можешь пройтиshow create tableсмотреть

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

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

Как видите, механизм хранения по умолчаниюInnoDB.

Если ваш механизм хранения хочет быть заменен, вы можете использовать

alter table cxuan003 engine = myisam;

заменить, после завершения замены он будет отображать0 rows affected, а по факту успешно эксплуатировался

Мы используемshow create tableПосмотрите на sql таблицы, чтобы знать

Функции механизма хранения

Далее будут представлены несколько часто используемых механизмов хранения и их основные характеристики.Эти механизмы хранения: **MyISAM, InnoDB, MEMORY и MERGE**.

MyISAM

До версии 5.1 MyISAM был механизмом хранения MySQL по умолчанию. MyISAM имел плохой параллелизм и использовался в нескольких сценариях. Основные функции:

  • не поддерживается事务эксплуатации характеристики ACID не существуют, эта конструкция предназначена для производительности и эффективности.

  • не поддерживается外键Операция, если принудительно добавить внешний ключ, MySQL не сообщит об ошибке, но внешний ключ не работает.

  • Степень детализации блокировки MyISAM по умолчанию составляет表级锁, поэтому производительность параллелизма низкая, блокировки выполняются быстрее, конфликтов блокировок меньше, а вероятность взаимоблокировок меньше.

  • MyISAM будет хранить на диске три файла, имя файла и имя таблицы совпадают, расширение.frm(存储表定义),.MYD(MYData,存储数据),MYI(MyIndex,存储索引). Здесь важно отметить, что MyISAM кэширует только索引文件, не кэширует файлы данных.

  • Типы индексов, поддерживаемые MyISAM:全局索引(Full-Text),B-Tree 索引,R-Tree 索引

    Полнотекстовый индекс: Появился для решения проблемы низкой эффективности нечеткого запроса текста.

    Индекс B-дерева: все узлы индекса хранятся в соответствии со структурой данных сбалансированного дерева, и все узлы данных индекса находятся в листовых узлах.

    Индекс R-Tree: его метод хранения отличается от индекса B-Tree. Он в основном предназначен для хранения пространства и многомерных полей данных для индексации. Текущая версия MySQL поддерживает только поля типа геометрии для индексации. По сравнению с BTREE, RTREE преимуществом является поиск диапазона.

  • Если хост, на котором расположена база данных, выходит из строя, файлы данных MyISAM легко повреждаются, и их трудно восстановить.

  • Добавление, удаление, изменение и производительность запросов: SELECT обладает высокой производительностью и подходит для многих запросов.

InnoDB

Начиная с MySQL 5.1 механизмом хранения по умолчанию стал механизм хранения InnoDB.По сравнению с MyISAM механизм хранения InnoDB претерпел серьезные изменения.Его основные функции:

  • Поддержка операций с транзакциями с функцией изоляции ACID транзакций, уровень изоляции по умолчанию —可重复读(repetable-read),пройти черезMVCC（并发版本控制）быть реализованным. в состоянии решить脏读и不可重复读Проблема.
  • InnoDB поддерживает операции с внешним ключом.
  • Детализация блокировки InnoDB по умолчанию行级锁, производительность параллелизма лучше, и возникнет взаимоблокировка.
  • Как и MyISAM, механизм хранения InnoDB также имеет.frm文件存储表结构Определения, но разница в том, что данные таблицы InnoDB и данные индекса хранятся вместе, и они расположены на B+ конечных узлах, в то время как данные таблицы MyISAM и данные индекса разделены.
  • InnoDB имеет безопасный файл журнала, который используется для восстановления потерянных данных из-за сбоев базы данных или других ситуаций для обеспечения согласованности данных.
  • Типы индексов, поддерживаемые InnoDB и MyISAM, одинаковы, но конкретная реализация сильно отличается из-за разных файловых структур.
  • С точки зрения производительности добавления, удаления, модификации и запросов, если выполняется большое количество добавлений, удалений и модификаций, рекомендуется использовать механизм хранения InnoDB, который удаляет строки во время операций удаления и не перестраивает таблицы.

MEMORY

Механизм хранения MEMORY использует то, что хранится в памяти, для создания таблиц. Каждая таблица MEMORY фактически соответствует только одному файлу на диске, формат.frm. Доступ к таблице MEMORY осуществляется быстро, поскольку ее данные хранятся в памяти. Использовать по умолчаниюHASH 索引.

MERGE

Механизм хранения MERGE представляет собой комбинацию набора таблиц MyISAM. Сама таблица MERGE не содержит данных. Операции запроса, обновления и удаления таблиц типа MERGE фактически выполняются во внутренних таблицах MyISAM. Таблица MERGE содержит два файла на диске, один.frmопределение таблицы хранения файлов, одно из них.MRGВ файле хранится состав таблицы MERGE и т.д.

Выберите правильный механизм хранения

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

  • MyISAM: если приложение обычно основано на поиске, с небольшим количеством операций вставки, обновления и удаления, а целостность и параллелизм вещей не очень высоки, обычно рекомендуется выбирать механизм хранения MyISAM.
  • InnoDB: если используются внешние ключи, требуется высокая степень параллелизма и требования к согласованности данных высоки, обычно выбирается механизм InnoDB.Как правило, крупные интернет-компании предъявляют высокие требования к параллелизму и целостности данных, поэтому механизм хранения InnoDB обычно используется.
  • ПАМЯТЬ: Механизм хранения ПАМЯТИ хранит все данные в памяти и может обеспечить чрезвычайно быстрый доступ, когда требуется быстрое позиционирование. ПАМЯТЬ обычно используется для небольших таблиц, которые обновляются реже, для быстрого доступа к результатам.
  • MERGE: MERGE использует таблицу MyISAM для внутреннего использования.Преимущество таблицы MERGE заключается в том, что она может преодолеть ограничение размера одной таблицы MyISAM, а путем распределения разных таблиц на нескольких дисках можно эффективно повысить эффективность доступа к таблице MERGE. .

Выберите подходящий тип данных

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

Выбор CHAR и VARCHAR

char и varchar — это два типа данных, которые мы часто используем для хранения строк. char обычно хранит строки фиксированной длины, которые представляют собой типы символов фиксированной длины, например следующие:

ценность char(5) байты памяти
'' ' ' 5 байт
'cx' 'cx ' 5 байт
'cxuan' 'cxuan' 5 байт
'cxuan007' 'cxuan' 5 байт

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

Обратите внимание: если MySQL использует не-严格模式Если это так, последняя строка таблицы выше может быть сохранена. Если MySQL использует严格模式Если да, то последняя строка таблицы над хранилищем сообщит об ошибке.

Если используется тип символа varchar, давайте посмотрим пример

ценность varchar(5) байты памяти
'' '' 1 байт
'cx' 'cx ' 3 байта
'cxuan' 'cxuan' 6 байт
'cxuan007' 'cxuan' 6 байт

Как видите, если используется varchar, сохраненные байты будут сохранены в соответствии с фактическим значением. Вы можете удивиться, почему длина varchar равна 5, а хранить нужно 3 байта или 6 байт, это потому, что при использовании типа данных varchar для хранения длина строки будет добавлена ​​в конце по умолчанию, занимая 1 слово Section ( два байта используются, если длина объявления столбца превышает 255). varchar не заполняет пустые строки.

Обычно используйте char для хранения строк фиксированной длины, таких какИдентификационный номер, номер мобильного телефона, электронная почта и т. д.;Используйте varchar для хранения строк переменной длины. Поскольку длина char фиксирована, скорость его обработки намного выше, чем у VARCHAR, но недостатком является то, что он тратит впустую место для хранения, но с непрерывным развитием версии MySQL производительность типа данных varchar также постоянно улучшается и улучшается. , поэтому во многих приложениях чаще используется тип VARCHAR.

В MySQL разные механизмы хранения используют разные принципы для CHAR и VARCHAR.

  • MyISAM: рекомендуется использовать столбцы данных фиксированной длины вместо столбцов данных переменной длины, то есть CHAR.
  • ПАМЯТЬ: используйте фиксированную длину для обработки, и CHAR, и VARCHAR обрабатываются как CHAR.
  • InnoDB: рекомендуется тип VARCHAR

ТЕКСТ и BLOB

Как правило, при сохранении меньшего количества текста мы выбираем CHAR и VARCHAR.При сохранении больших объемов текста мы часто выбираем TEXT и BLOB; основное различие между TEXT и BLOB заключается в том, что BLOB может сохранять二进制数据; а ТЕКСТ может сохранять только字符数据, ТЕКСТ подразделяется, чтобы иметь

  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

BLOB подразделяется на

  • BLOB
  • MEDIUMBLOB
  • LONGBLOB

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

TEXT и BLOB будут иметь некоторые проблемы с производительностью после удаления данных.Для повышения производительности рекомендуется использоватьOPTIMIZE TABLEФункция дефрагментации таблицы.

Синтетические индексы также можно использовать для повышения производительности запросов к текстовым полям (BLOB и TEXT). Синтетический индекс предназначен для создания хеш-значения на основе содержимого больших текстовых полей (BLOB и TEXT) и сохранения этого значения в соответствующем столбце, чтобы соответствующая строка данных могла быть найдена в соответствии с хэш-значением. Обычно используются такие алгоритмы хэширования, как md5() и SHA1(). Если строки, сгенерированные алгоритмом хеширования, имеют пробелы в конце, не сохраняйте их в CHAR и VARCHAR. Давайте посмотрим на это использование.

Сначала создайте таблицу, в которую записываются поля больших двоичных объектов и хэш-значения.

Вставьте данные в cxuan005, где хеш-значение используется в качестве хеш-значения информации.

Затем вставьте еще два данных

Вставьте часть данных с информацией как cxuan005

Если вы хотите запросить данные, информация о которых равна cxuan005, вы можете запросить хеш-столбец для запроса

Это пример синтетического индекса.Если вы хотите выполнять нечеткие запросы к BLOB, вы будете использовать префиксный индекс.

Другие способы оптимизации BLOB и TEXT:

  • Не извлекайте индексы BLOB и TEXT без необходимости
  • Разделите столбцы BLOB или TEXT в отдельные таблицы.

Выбор чисел с плавающей запятой и фиксированной запятой

Числа с плавающей запятой относятся к значениям, которые содержат десятичные дроби.После того, как число с плавающей запятой вставляется в указанный столбец за пределами указанной точности, число с плавающей запятой будет округлено в большую сторону.Число с плавающей запятой в MySQL относится кfloatиdouble, число с фиксированной точкой относится кdecimal, числа с фиксированной точкой могут сохранять и отображать данные более точно. Ниже приведен пример, объясняющий точность чисел с плавающей запятой.

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

Затем вставьте два данных

Затем выполните запрос, вы увидите, что округление двух данных, полученных из запроса, отличается.

Чтобы ясно увидеть точность чисел с плавающей запятой и чисел с фиксированной запятой, давайте посмотрим на другой пример.

Сначала измените два поля cxuan006 на одинаковую длину и десятичные разряды.

Затем вставьте два данных

Выполните операцию запроса, вы можете обнаружить, что числа с плавающей запятой будут вызывать ошибки по сравнению с числами с фиксированной запятой.

Выбор типа даты

В MySQL типы дат представленыДАТА, ВРЕМЯ, ДАТАВРЕМЯ, TIMESTAMP,существует

138 картинок, которые познакомят вас с MySQL

Различие между типами даты было представлено в этой статье, и мы не будем здесь подробно останавливаться на нем. Нижеследующее в основном знакомит с выбором

  • TIMESTAMP связан с часовым поясом и может лучше отражать текущее время. Если записанная дата должна использоваться людьми в разных часовых поясах, лучше использовать TIMESTAMP.
  • DATE используется для представления года, месяца и дня.Если фактическое значение приложения необходимо сохранить год, месяц и день, можно использовать DATE.
  • ВРЕМЯ используется для представления часов, минут и секунд.Если фактическое значение приложения необходимо сохранить в часах, минутах и ​​секундах, можно использовать ВРЕМЯ.
  • ГОД используется для представления года, ГОД имеет 2 цифры (предпочтительно 4 цифры) и год в 4-значном формате. По умолчанию 4 бита. Если фактическое приложение сохраняет только год, совершенно нормально хранить тип YEAR с 1 байтом. Это может не только сэкономить место для хранения, но и повысить эффективность работы стола.

Набор символов MySQL

Давайте взглянем на набор символов MySQL.Вкратце, набор символов — это набор текстовых символов, правил кодирования и сравнения. В 1960 году американская организация по стандартизации ANSI выпустила первый компьютерный набор символов, известный какASCII(American Standard Code for Information Interchange). Начиная с кодировки ASCII, каждая страна и международная организация изучили собственный набор символов, напримерISO-8859-1,GBKЖдать.

Но каждая страна использует свой набор символов, что создает большие трудности с переносимостью. Итак, чтобы использовать Unicode,国际标准化组织(ISO)Указывает единый стандарт символов — кодировку Unicode, которая поддерживает почти все кодировки символов. Вот некоторые распространенные кодировки символов

набор символов фиксированная длина Кодирование
ASCII да Однобайтовое 7-битное кодирование
ISO-8859-1 да Однобайтовое 8-битное кодирование
GBK да двухбайтовое кодирование
UTF-8 нет 1 - 4 байтовая кодировка
UTF-16 нет 2-байтовая или 4-байтовая кодировка
UTF-32 да 4-байтовая кодировка

Для базы данных очень важен набор символов, потому что большая часть данных, хранящихся в базе данных, представляет собой всевозможные символы, а набор символов очень важен для хранения, производительности и системного переноса базы данных.

MySQL поддерживает множество наборов символов, вы можете использоватьshow character set;чтобы увидеть все доступные наборы символов

или использовать

select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;

Проверять.

использоватьinformation_schema.character_setчтобы увидеть наборы символов и правила сопоставления.

Дизайн и использование индексов

Выше мы представили несколько типов индексов и объяснили различные типы индексов, разъяснили преимущества и недостатки и т. д. Давайте поговорим об индексах с точки зрения дизайна.Об индексах вы должны знать:Индексы являются наиболее распространенным инструментом, используемым базами данных для повышения производительности..

Обзор индекса

Все типы MySQL могут быть проиндексированы, и использование индексов для связанных столбцов может улучшитьSELECTЛучший способ запросить производительность. И MyISAM, и InnoDB используютBTREEВ качестве индекса MySQL 5 не поддерживает函数索引, но поддерживает前缀索引.

Индекс префикса, как следует из его названия, индексирует префикс полей столбца Длина индекса префикса связана с механизмом хранения. Длина индекса префикса MyISAM поддерживает до 1000 байт, а длина индекса префикса InnoDB поддерживает до байтов 767. Чем ниже повторяемость значений индекса, тем выше эффективность запроса.

В MySQL в основном есть следующие индексы

  • 全局索引(FULLTEXT): Глобальный индекс, в настоящее время только механизм MyISAM поддерживает глобальный индекс, он, кажется, решает проблему низкой эффективности нечеткого запроса для текста и ограничен столбцами CHAR, VARCHAR и TEXT.
  • 哈希索引(HASH): Хэш-индекс — это структура данных уникальных пар ключ-значение, используемая в MySQL, которая очень подходит в качестве индекса. HASH-индекс имеет преимущество одноразового позиционирования, ему не нужно искать узел за узлом, как дерево, но такой вид поиска подходит для случая поиска одиночного ключа, для поиска по диапазону, производительности HASH-индекса будет очень низким. По умолчанию механизм хранения MEMORY использует индексы HASH, но индексы BTREE также поддерживаются.
  • B-Tree 索引: B означает Balance, BTree — это сбалансированное дерево, у него много вариантов, наиболее распространенным является B+ Tree, которое широко используется MySQL.
  • R-Tree 索引: R-Tree редко используется в MySQL и поддерживает только тип данных геометрии. Единственными механизмами хранения, которые поддерживают этот тип, являются MyISAM, BDb, InnoDb, NDb и Archive. По сравнению с B-Tree, R-Tree имеет преимущество в области действия. , Найдите.

Индекс может быть создан при создании таблицы, а может быть создан отдельно.Создадим его отдельно.Создаем префиксный индекс на cxuan004

Мы используемexplainДля анализа можно посмотреть, как cxuan004 использует индекс

Если вы не хотите использовать индекс, вы можете удалить индекс, синтаксис удаления индекса

Принципы построения индекса

При создании индекса постарайтесь учитывать следующие принципы, чтобы повысить эффективность использования индекса.

  • выберите索引位置, наиболее подходящая позиция для выбора индекса должна отображаться вwhereстолбцы в заявлении вместоselectСтолбец в списке выбора после ключевого слова.
  • выбрать использование唯一索引, как следует из названия, значение уникального индекса является уникальным, что позволяет быстрее определить запись, например, номер студента подходит для использования уникального индекса, но пол студента не подходит, потому что независимо от того, какое значение ищется, есть почти половина строки.
  • Создавайте индексы для часто используемых полей. Если поле часто используется в качестве условия запроса, скорость запроса этого поля сильно повлияет на скорость запроса всей таблицы. Поэтому построение индекса для такого поля может улучшить запрос вся таблица скорость.
  • Не переиндексируйте, ограничьте количество индексов, чем больше индексов, тем лучше, каждый индекс будет занимать место на диске, чем больше индексов, тем больше места на диске требуется.
  • использовать как можно больше前缀索引, если значение индекса очень длинное, это повлияет на скорость запроса. В настоящее время для индексации определенных символов столбца следует использовать индекс префикса, что может повысить эффективность поиска.
  • С крайним левым префиксом, когда вы создаете индекс с n столбцами, вы фактически создаете n индексов, которые может использовать MySQL. Многостолбцовый индекс может функционировать как несколько индексов, используя крайний левый столбец индекса для сопоставления строк. Такой столбец называется крайним левым префиксом.
  • Для таблиц, использующих механизм хранения InnoDB, записи сохраняются в определенном порядке. Если есть четкое определение первичного ключа, то оно будет сохранено в порядке первичного ключа, если нет первичного ключа, но есть уникальный индекс, то оно будет сохранено в порядке уникального индекса. Если нет ни первичного ключа, ни уникального индекса, в таблице будет автоматически сгенерирован внутренний столбец и сохранен в порядке этого столбца. В общем, порядок с использованием первичного ключа самый быстрый
  • Удалить индексы, которые больше не используются или используются редко

Посмотреть

MySQL предоставляет функцию просмотра с версии 5.0, и мы представим функцию просмотра ниже.

что такое вид

Английское название видаview, который является виртуальной таблицей. Представление прозрачно для пользователя, оно фактически не существует в базе данных.Представление представляет собой таблицу, динамически составленную из строк и столбцов базы данных, так в чем преимущества представления по сравнению с таблицей базы данных?

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

  • Использование представлений может упростить операции: при использовании представлений нам не нужно обращать внимание на определение структуры таблицы, мы можем определить часто используемые наборы данных как представления, что может упростить операции.
  • Безопасность: пользователь не может изменить или удалить представление по своему желанию, что может обеспечить безопасность данных.
  • Независимость от данных: как только структура представления определена, влияние изменения структуры таблицы на пользователя может быть защищено, а добавление столбцов в таблицу базы данных не влияет на представление; оно имеет определенную степень независимость

Операции с представлениями

Операции просмотра включают создание или изменение представлений, удаление представлений и просмотр определений представлений.

Создание или изменение представлений

использоватьcreate viewчтобы создать представление

Для демонстрационной функции давайте сначала создадим таблицу.productТаблица, есть три поля, идентификатор, имя, цена, ниже приводится инструкция для создания таблицы

create table product(id int(11),name varchar(20),price float(10,2));

Затем вставляем в него несколько кусочков данных

insert into product values(1, "apple","3.5"),(2,"banana","4.2"),(3,"melon","1.2");

Структура таблицы после вставки следующая

Затем мы создаем представление

create view v1 as select * from product;

Затем мы смотрим на структуру представления v1

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

Посмотреть использование

show tables;

Также смотрите все представления.

Синтаксис удаления представления:

drop view v1;

можно удалить напрямую.

Представления имеют другие операции, такие как операции запроса

вы также можете использовать

describe v1;

Посмотреть структуру таблицы

Обновление просмотра

update v1 set name = "grape" where id = 1;

хранимая процедура

MySQL поддерживает хранимые процедуры и функции с версии 5.0.

Так что же такое хранимая процедура?

Хранимая процедура — это набор операторов SQL, которые выполняют определенный набор функций в системе базы данных., который хранится в системе баз данных и остается действительным после одной компиляции. Итак, каковы преимущества использования хранимых процедур?

  • Использование хранимых процедур инкапсулировано и может скрыть сложную логику SQL.
  • Хранимые процедуры могут получать параметры и возвращать результаты.
  • Хранимые процедуры имеют очень высокую производительность и обычно используются для пакетного выполнения операторов.

Каковы недостатки использования хранимых процедур?

  • Написание сложной хранимой процедуры
  • Хранимые процедуры сильно зависят от базы данных и имеют плохую переносимость.

Использование хранимой процедуры

Создание хранимой процедуры

Поняв, что такое хранимая процедура, давайте воспользуемся хранимой процедурой.Здесь нам нужно понять небольшую хитрость, то естьdelimiterИспользование разделителя используется для настройки терминатора, что это значит, если вы используете

delimiter ?

, затем вы используете в конце инструкции sql;Не можете заставить операторы SQL выполняться, не верите мне? мы можем посмотреть на

Как видите, мы использовали в конце строки в операторе SQL;Но мы не видели результата выполнения. Ниже мы используем

delimiter ;

Восстановите условия выполнения по умолчанию и посмотрите еще раз

Сначала мы создаем хранимую процедуру;заменить?, ниже приведен оператор создания хранимой процедуры

mysql> delimiter ?
mysql> create procedure sp_product()
    -> begin
    -> select * from product;
    -> end ?

Хранимая процедура на самом деле является функцией, поэтому после ее создания мы можем использоватьcallметод для вызова этой хранимой процедуры

Поскольку мы определили использование разделителя ? в конце, его следует использовать и здесь.

Хранимые процедуры также могут принимать параметры, например, мы определяем случай получения параметров

Затем мы используемcallвызвать эту хранимую процедуру

Как видите, когда мы вызываем id = 2, оператор SQL хранимой процедуры эквивалентен следующему:

select * from product where id = 2;

Поэтому запрашиваются только результаты с id = 2.

Хранимая процедура удалить

За один раз можно удалить только одну хранимую процедуру.Синтаксис удаления хранимой процедуры следующий:

drop procedure sp_product ;

Просто используйте sp_product напрямую, не нужно добавлять().

Представление хранимой процедуры

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

мы можем использовать

show create procedure proc_name;

использование переменных

В MySQL переменные можно разделить на две категории, а именно系统变量и用户变量, что является грубым разделением. Но в соответствии с реальным приложением он подразделяется на четыре типа, а именно: локальные переменные, пользовательские переменные, переменные сеанса и глобальные переменные.

пользовательская переменная

Пользовательские переменные основаны на会话变量Реализовано, может быть временно сохранено, пользовательские переменные связаны с соединением, то есть определяемая клиентом переменная не видна другим клиентам. Ссылка автоматически освобождается при выходе клиента. мы можем использоватьsetоператор устанавливает переменную

set @myId = "cxuan";

затем используйтеselectУсловие запроса может запрашивать пользовательскую переменную, которую мы только что установили.

Пользовательская переменная связана с клиентом.При выходе эта переменная автоматически исчезнет.Теперь мы выходим из клиента.

exit

Теперь повторно заходим в клиент и снова используемselectУсловный запрос

Выяснил, что его больше нет@myId.

локальная переменная

Локальные переменные в MySQL очень похожи на Java.Локальная переменная в Java — это метод или блок кода, в котором находится Java, а областью действия локальной переменной в MySQL является хранимая процедура, в которой она находится. Использование локальной переменной MySQLdeclareобъявить.

переменная сеанса

Сервер поддерживает переменную сеанса для каждого подключенного клиента. можно использовать

show session variables;

Показать все переменные сеанса.

Мы можем вручную установить переменные сеанса

set session auto_increment_increment=1;

或者使用

set @@session.auto_increment_increment=2;

Затем сделайте запрос, запросите переменную сеанса, используя

или использовать

глобальная переменная

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

можно использовать

show global variables;

Просмотр глобальных переменных

Глобальные переменные можно установить следующими двумя способами.

set global sql_warnings=ON;        -- global不能省略

/** 或者 **/

set @@global.sql_warnings=OFF;

При запросе глобальных переменных вы можете использовать

или

Введение в операторы процесса MySQL

MySQL поддерживает следующие операторы управления

  • IF

IF используется для реализации логического суждения и выполнения различных операторов SQL при выполнении различных условий.

IF ... THEN ...
  • CASE

Реализация CASE немного сложнее, чем IF, синтаксис следующий

CASE ...
	WHEN ... THEN...
	...
END CASE

Операторы CASE также могут быть выполнены с использованием IF

  • LOOP

LOOP используется для реализации простых циклов

label:LOOP
     ...
END LOOP label;

если...Если вы не пишете оператор SQL в , то это простой оператор бесконечного цикла

  • LEAVE

Используется для обозначения выхода из отмеченной конструкции процесса, обычно используется с BEGIN...END или циклами.

  • ITERATE

Оператор ITERATE должен использоваться в цикле, чтобы пропустить оставшуюся часть текущего цикла и сразу перейти к следующему циклу.

  • REPEAT

Оператор управления циклом с условием выхода из цикла при выполнении условия.

REPEAT
   ...
   UNTIL
END REPEAT;
  • WHILE

Значение оператора WHILE почти такое же, как и у REPEAT.Разница между циклом WHILE и циклом REPEAT заключается в следующем: WHILE выполняет цикл только при выполнении условий, а REPEAT выходит из цикла при выполнении условий;

курок

MySQL поддерживается с версии 5.0触发器, Триггеры обычно воздействуют на таблицу, запускаются при выполнении определенных условий и выполняют набор операторов, определенных в триггере. Давайте вместе рассмотрим триггер.

Давайте возьмем пример для распознавания триггеров: например, если у вас есть таблица журнала и таблица сумм, вы будете записывать таблицу журнала каждый раз, когда вводите сумму.Что вы будете делать? Вставлять данные в таблицу Amount и таблицу Log одновременно? Если есть триггер, вы можете напрямую вводить данные в таблицу сумм, и запись журнала будет автоматически вставлена ​​в таблицу журнала.Конечно, триггеры имеют не только новые операции, но также операции обновления и удаления.

Создать триггер

Мы можем создать триггеры следующим образом

create trigger triggername triggertime triggerevent on tbname for each row triggerstmt

Есть несколько параметров, связанных с вышеизложенным, я знаю, что вы немного запутались, пожалуйста, объясните.

  • triggername: это относится к имени триггера
  • triggertime: Это относится к времени запуска, котороеBEFOREвсе ещеAFTER
  • triggerevent: Это относится к триггерному событию. Существует три события:ВСТАВИТЬ, ОБНОВИТЬ или УДАЛИТЬ.
  • tbname: Этот параметр относится к имени таблицы, созданной триггером, на какой таблице он создан.
  • triggerstmt: тело триггера, то есть оператор SQL.

Итак, можно создать шесть видов триггеров.

ПЕРЕД ВСТАВКОЙ, ПОСЛЕ ВСТАВКИ, ПЕРЕД ОБНОВЛЕНИЕМ, ПОСЛЕ ОБНОВЛЕНИЯ, ПЕРЕД УДАЛЕНИЕМ, ПОСЛЕ УДАЛЕНИЯ

вышеfor each nowУказывает, что любое действие над записью вызовет срабатывание триггера.

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

Мы по-прежнему используем приведенную выше таблицу procuct в качестве примера, мы создаем таблицу информации о продукте product_info.

create table product_info(p_info varchar(20));

Затем мы создаемtrigger

Вставляем кусок данных в таблицу товаров

insert into product values(4,"pineapple",15.3);

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

Мы не вставляли данные в таблицу product_info Теперь давайте взглянем на таблицу product_info Мы ожидаем, что данные есть Рассмотрим ее подробно.

Когда были введены эти данные? мы создаем триггерыtg_pinfoЭти данные были вставлены, когда .

удалить триггер

Можно использовать триггерыdropДля удаления используется следующий синтаксис удаления.

drop trigger tg_pinfo;

Синтаксис для удаления таблицы такой же

Посмотреть триггеры

Мы часто смотрим на триггеры, которые можно сделать, выполнивshow triggersКоманда для просмотра состояния триггера, синтаксиса и другой информации.

Другой способ запроса - запросить таблицуinformation_schema.triggersТаблица, это может запрашивать указанную информацию указанного триггера, что намного удобнее в работе.

Роль триггера

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

Примечание. Существует два ограничения на использование триггеров.

  1. Триггеры не могут вызывать хранимые процедуры, которые возвращают данные клиенту. Вы также не можете использовать динамические операторы SQL для операторов CALL.
  2. Операторы не могут быть запущены и завершены в триггерах, таких как START TRANSACTION

Категории