MySQL — это просто!


Оглавление (нажмите, чтобы открыть):

Wiki-учебник по веб-технологиям: MySQL .

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

Базы данных: основные понятия

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

Например, мы используем записную книжку для хранения номеров телефонов своих друзей и планирования своего времени. Телефонная книга содержит информацию о людях, живущих в одном городе. Все это своего рода базы данных. Ну а раз это базы данных, то посмотрим, как в них хранятся данные. Например, телефонная книга представляет собой таблицу (табл. 10.1).

В этой таблице данные – это собственно номера телефонов, адреса и ФИО., т.е. строки «Иванов Иван Иванович», « 32–43–12 » и т.п., а названия столбцов этой таблицы, т.е. строки «ФИО», «Номер телефона» и «Адрес» задают смысл этих данных, их семантику.

Таблица 10.1. Пример базы данных: телефонная книга

ФИО Номер телефона Адрес
Иванов Иван Иванович 32–43–12 ул. Ленина, 12, 43
Ильин Федор Иванович 32–32–34 пр. Маркса, 32, 45

Теперь представьте, что записей в этой таблице не две, а две тысячи, вы занимаетесь созданием этого справочника и где-то произошла ошибка (например, опечатка в адресе). Видимо, тяжеловато будет найти и исправить эту ошибку вручную. Нужно воспользоваться какими-то средствами автоматизации. Для управления большим количеством данных программисты (не без помощи математиков) придумали системы управления базами данных (СУБД). По сравнению с текстовыми базами данных электронные СУБД имеют огромное число преимуществ, от возможности быстрого поиска информации, взаимосвязи данных между собой до использования этих данных в различных прикладных программах и одновременного доступа к данным нескольких пользователей.

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

База данных – это совокупность связанных данных, организованных по определенным правилам, предусматривающим общие принципы описания, хранения и манипулирования, независимая от прикладных программ. База данных является информационной моделью предметной области. Обращение к базам данных осуществляется с помощью системы управления базами данных (СУБД). СУБД обеспечивает поддержку создания баз данных, централизованного управления и организации доступа к ним различных пользователей.

Итак, мы пришли к выводу, что хранить данные независимо от программ, так, что они связаны между собой и организованы по определенным правилам, целесообразно. Но вопрос, как хранить данные, по каким правилам они должны быть организованы, остался открытым. Способов существует множество (кстати, называются они моделями представления или хранения данных). Наиболее популярные – объектная и реляционная модели данных.

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

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

Примеры реляционных СУБД: Mysql, PostgreSql. В основу объектной модели положена концепция объектно-ориентированного программирования, в которой данные представляются в виде набора объектов и классов, связанных между собой родственными отношениями, а работа с объектами осуществляется с помощью скрытых (инкапсулированных) в них
методов.

Примеры объектных СУБД: Cache, GemStone (от Servio Corporation), ONTOS (ONTOS).

В последнее время производители СУБД стремятся соединить два этих подхода и проповедуют объектно-реляционную модель представления данных. Примеры таких СУБД – IBM DB2 for Common Servers, Oracle8.

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

1. Ключи

Для начала давайте подумаем над таким вопросом: какую информацию нужно дать о человеке, чтобы собеседник точно сказал, что это именно тот человек, сомнений быть не может, второго такого нет? Сообщить фамилию, очевидно, недостаточно, поскольку существуют однофамильцы. Если собеседник человек, то мы можем приблизительно объяснить, о ком речь, например вспомнить поступок, который совершил тот человек, или еще как-то. Компьютер же такого объяснения не поймет, ему нужны четкие правила, как определить, о ком идет речь. В системах управления базами данных для решения такой задачи ввели понятие первичного ключа.

Первичный ключ (primary key, PK) – минимальный набор полей, уникально идентифицирующий запись в таблице. Значит, первичный ключ – это в первую очередь набор полей таблицы, во-вторых, каждый набор значений этих полей должен определять единственную запись (строку) в таблице и, в-третьих, этот набор полей должен быть минимальным из всех обладающих таким же свойством.

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

Например, в нашей таблице (см. выше) ФИО и адрес позволяют однозначно выделить запись о человеке. Если же говорить в общем, без связи с решаемой задачей, то такие знания не позволяют точно указать на единственного человека, поскольку существуют однофамильцы, живущие в разных городах по одному адресу. Все дело в границах, которые мы сами себе задаем. Если считаем, что знания ФИО, телефона и адреса без указания города для наших целей достаточно, то все замечательно, тогда поля ФИО и адрес могут образовывать первичный ключ. В любом случае проблема создания первичного ключа ложится на плечи того, кто проектирует базу данных (разрабатывает структуру хранения данных). Решением этой проблемы может стать либо выделение характеристик, которые естественным образом определяют запись в таблице (задание так называемого логического, или естественного, PK), либо создание дополнительного поля, предназначенного именно для однозначной идентификации записей в таблице (задание так называемого суррогатного, или искусственного, PK).

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

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

Например, у нас есть три таблицы: содержащая информацию об исторических личностях (Persons), содержащая информацию об их изобретениях (Artifacts) и содержащая изображения как личностей, так и артефактов (Images) (рис 10.1).

Первичным ключом во всех этих таблицах является поле >

2. Индексирование

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

Для этого (для оптимизации производительности запросов) производят индексирование некоторых полей таблицы. Использовать индексы полезно для быстрого поиска строк с указанным значением одного столбца. Без индекса чтение таблицы осуществляется по всей таблице, начиная с первой записи, пока не будут найдены соответствующие строки. Чем больше таблица, тем больше накладные расходы. Если же таблица содержит индекс по рассматриваемым столбцам, то база данных может быстро определить позицию для поиска в середине файла данных без просмотра всех данных. Это происходит потому, что база данных помещает проиндексированные поля поближе в памяти, так, чтобы можно было побыстрее найти их значения. Для таблицы, содержащей 1000 строк, это будет как минимум в 100 раз быстрее по сравнению с последовательным перебором всех записей. Однако в случае, когда необходим доступ почти ко всем 1000 строкам, быстрее будет последовательное чтение, так как при этом не требуется операций поиска по диску. Так что иногда индексы бывают только помехой. Например, если копируется большой объем данных в таблицу, то лучше не иметь никаких индексов. Однако в некоторых случаях требуется задействовать сразу несколько индексов (например, для обработки запросов к часто используемым таблицам).

Если говорить о Mysql, то там существует три вида индексов: PRIMARY, UNIQUE, и INDEX, а слово ключ (KEY) используется как синоним слова индекс (INDEX). Все индексы хранятся в памяти в виде B-деревьев.

PRIMARY – уникальный индекс (ключ) с ограничением, что все индексированные им поля не могут иметь пустого значения (т.е. они NOT NULL). Таблица может иметь только один первичный индекс, но он может состоять из нескольких полей.

UNIQUE – ключ (индекс), задающий поля, которые могут иметь только уникальные значения.

INDEX – обычный индекс (как мы описали выше). В Mysql, кроме того, можно индексировать строковые поля по заданному числу символов от начала строки.

3. СУБД Mysql

Продолжим разговор о СУБД Mysql. Mysql – это реляционная система управления базами данных. То есть данные в ее базах хранятся в виде логически связанных между собой таблиц, доступ к которым осуществляется с помощью языка запросов SQL. Mysql – свободно распространяемая система, т.е. платить за ее применение не нужно. Кроме того, это достаточно быстрая, надежная и, главное, простая в использовании СУБД, вполне подходящая для не слишком глобальных проектов.

Работать с Mysql можно не только в текстовом режиме, но и в графическом. Существует очень популярный визуальный интерфейс (кстати, написанный на PHP) для работы с этой СУБД. Называется он PhpMyAdmin. Этот интерфейс позволяет значительно упростить работу с базами данных в Mysql.

PhpMyAdmin позволяет пользоваться всеми достоинствами браузера, включая прокрутку изображения, если оно не умещается на экран. Многие из базовых SQLфункций работы с данными в PhpMyAdmin сведены к интуитивно понятным интерфейсам и действиям, напоминающим переход по ссылкам в Internet. Но, тем не менее, стоит все же поработать и в текстовом режиме.

Перед тем как переходить к детальному изучению языка SQL, несколько слов об установке Mysql и подготовке к работе. Если вы не собираетесь заниматься администрированием сервера, то информация, приведенная ниже, пригодится вам только для общего развития. Итак, устанавливается Mysql очень просто – автоматически, пару раз нажмите OK, и все. После этого вы можете зайти в директорию, где лежат файлы типа Mysql.exe, Mysqld.exe и т.п. (у нас под Windows XP это c:\Mysql\bin) Последний файл запускает Mysql-сервер. В некоторых системах сервер запускается в виде сервиса. После запуска сервера следует запустить Mysql-клиент, запустив программу Mysql.exe. Здесь даже пароля не спросят. Более того, если вы наберете shell> Mysql.exe -u root или shell>Mysql -u root Mysql то получите все права администратора Mysql сервера. Кстати, выполнять эти команды надо, находясь в той директории, где лежат файлы Mysql.exe.
Для начала, не вдаваясь в подробности команд, исправим эти два недочета (отсутствие пароля у администратора и возможность входа анонимным пользователям):

Все данные о пользователях Mysql хранит в таблице user в специальной базе данных Mysql, доступ к которой имеет только администратор сервера. Поэтому, чтобы изменить какой-либо пароль, нужно изменить эту таблицу. Пароль задается с помощью функции PASSWORD, которая кодирует введенные данные. Кроме изменения пароля администратора, нужно еще удалить всех пользователей, не имеющих логина (команда DELETE). Команда Flush Privileges заставляет вступить в действие изменения, произошедшие в системной базе данных (Mysql).

Теперь создадим базу данных, с которой будем работать (мы все еще работаем как администратор сервера):
Mysql>create database book;

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

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

Команда GRANT наделяет пользователя nina, зашедшего на сервер с этой же машины (c localhost) и идентифицируемого паролем «123», определенными правами (в данном случае всеми) на все таблицы базы данных book. Теперь мы можем выйти и зайти как пользователь nina с соответствующим паролем:

Если вы собираетесь пользоваться базой данных на чужом сервере, то его администратор проделает все описанные выше действия за вас, т.е. все настроит и создаст пользователя и базу данных. В следующей главе описаны команды языка SQL, которые пригодятся для работы с данными, хранящимися в СУБД Mysql.

4. Язык SQL

5. Основные операторы языка SQL

6. Описание данных MySQL

7. Построение интерфейса для добавления информации

Итак, у нас есть какая-то таблица в базе данных. Чтобы построить интерфейс для добавления информации в эту таблицу, нужно ее структуру (т.е. набор ее полей) отобразить в html-форму.

Разобьем эту задачу на следующие подзадачи:

  • установка соединения с БД;
  • выбор рабочей БД;
  • получение списка полей таблицы;
  • отображение полей в html-форму.

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

Рассмотрим все эти задачи по порядку.

8. Установка соединения

Итак, первое, что нужно сделать, – это установить соединение с базой данных.

Воспользуемся функцией Mysql_connect.

Данная функция устанавливает соединение с сервером Mysql и возвращает указатель на это соединение или FALSE в случае неудачи.

Для отсутствующих параметров устанавливаются следующие значения по умолчанию:
server = ‘localhost:3306’
username = имя пользователя владельца процесса сервера
password = пустой пароль

Если функция вызывается дважды с одними и теми же параметрами, то новое соединение не устанавливается, а возвращается ссылка на старое соединение.

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

Параметр client_flags – это комбинация следующих констант:

Параметр new_link появился в PHP 4.2.0, а параметр client_flags – в PHP 4.3.0.

Соединение с сервером закрывается при завершении исполнения скрипта, если оно до этого не было закрыто с помощью функции Mysql_close().

Итак, устанавливаем соединение с базой данных на локальном сервере для пользователя nina с паролем “123”:

Действие Mysql_connect равносильно команде

9. Выбор базы данных

После установки соединения нужно выбрать базу данных, с которой будем работать. Наши данные хранятся в базе данных book. В Mysql выбор базы данных осуществляется с помощью команды use:

В PHP для этого существует функция Mysql_select_db.

Эта функция возвращает TRUE в случае успешного выбора базы данных и FALSE

  • в противном случае.

Сделаем базу данных book рабочей:

10. Получение списка полей таблицы

10.1. Синтаксис Mysql_list_fields

Эта функция возвращает список полей в таблице table_name в базе данных database_name. Получается, что выбирать базу данных нам было необязательно, но это пригодится позже. Как можно заметить, результат работы этой функции – переменная типа ресурс. То есть это не совсем то, что мы хотели получить. Это ссылка, которую можно использовать для получения информации о полях таблицы, включая их названия, типы и флаги.

Функция Mysql_field_name возвращает имя поля, полученного в результате выполнения запроса. Функция Mysql_field_len возвращает длину поля. Функция Mysql_field_type возвращает тип поля, а функция Mysql_field_flags возвращает список флагов поля, записанных через пробел. Типы поля могут быть int, real, string, blob и т.д. Флаги могут быть not_null, primary_key, unique_key, blob,
auto_increment и т.д.

Синтаксис у всех этих команд одинаков:

Здесь result – это идентификатор результата запроса (например, запроса, отправленного функциями Mysql_list_fields или Mysql_query (о ней будет рассказано позднее)), а field_offset – порядковый номер поля в результате.

Вообще говоря, то, что возвращают функции типа Mysql_list_fields или Mysql_query, представляет собой таблицу, а точнее, указатель на нее. Чтобы получить из этой таблицы конкретные значения, нужно задействовать специальные функции, которые построчно читают эту таблицу. К таким функциям и относятся Mysql_field_name и т.п. Чтобы перебрать все строки в таблице результата выполнения запроса, нужно знать число строк в этой таблице. Команда Mysql_num_rows(ресурс result) возвращает число строк во множестве результатов
result.

А теперь попробуем получить список полей таблицы Artifacts (коллекция экспонатов).

В результате должно получиться примерно вот что (если в таблице всего два поля, конечно):

Имя поля: id
Тип поля: int
Длина поля: 11
Строка флагов поля:
not_null primary_key auto_increment
Имя поля: title
Тип поля: string
Длина поля: 255
Строка флагов поля:
Отображение списка полей в html-форму

Теперь немножко подкорректируем предыдущий пример. Будем не просто выводить информацию о поле, а отображать его в подходящий элемент html- формы. Так, элементы типа BLOB переведем в textarea (заметим, что поле description, которое мы создавали с типом TEXT, отображается как имеющее тип BLOB), числа и строки отобразим в текстовые строки ввода , а элемент, имеющий метку автоинкремента, вообще не будем отображать, поскольку его значение устанавливается автоматически.

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

Эта функция разбивает строку string на части с помощью разделителя separator и возвращает массив полученных строк.

В нашем случае в качестве разделителя нужно взять пробел “ «, а в качестве исходной строки для разбиения – строку флагов поля.

Итак, создадим форму для ввода данных в таблицу Artifacts:

Листинг 11.0.1. Форма для ввода данных в таблицу Artifacts

11. Запись данных в базу данных

Итак, форма создана. Теперь нужно сделать самое главное – отправить данные из этой формы в нашу базу данных. Как вы уже знаете, для того чтобы записать данные в таблицу, используется команда INSERT языка SQL. Например:

Возникает вопрос, как можно воспользоваться такой командой (или любой другой командой SQL) в PHP скрипте. Для этого существует функция Mysql_query().

11.1. Синтаксис Mysql_query

Если открытые соединения отсутствуют, функция пытается соединиться с СУБД, аналогично функции Mysql_connect() без параметров. Результат запроса буферизируется.

Замечание: строка запроса НЕ должна заканчиваться точкой с запятой.

Только для запросов SELECT, SHOW, EXPLAIN, DESCRIBE, Mysql_query() возвращает указатель на результат запроса, или FALSE, если запрос не был выполнен. В остальных случаях Mysql_query() возвращает TRUE, если запрос выполнен успешно, и FALSE – в случае ошибки. Значение, не равное FALSE, говорит о том, что запрос был выполнен успешно. Оно не говорит о количестве затронутых или возвращенных рядов. Вполне возможна ситуация, когда успешный запрос не затронет ни одного ряда. Mysql_query() также считается ошибочным и вернет FALSE, если у пользователя недостаточно прав для работы с указанной в запросе таблицей.

Итак, теперь мы знаем, как отправить запрос на вставку строк в базу данных.

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

$_POST[‘имя_поля’].


Листинг 11.0.2. insert.php

Итак, задачу добавления данных с помощью web-интерфейса мы решили. Однако тут есть одна тонкость. При решении мы не учитывали тот факт, что значения некоторых полей (author, photo) должны браться из других таблиц (Persons, Images). Поскольку Mysql с внешними ключами не работает, этот момент остается на совести разработчиков системы, т.е. на нашей совести. Нужно дописать программу таким образом, чтобы была возможность вводить в такие поля правильные значения. Но мы делать этого не будем, поскольку задача лекции состоит в том, чтобы познакомить читателя с элементами технологии, а не в том, чтобы создать работающую систему. Кроме того, имеющихся у читателя знаний вполне достаточно, чтобы решить эту проблему самостоятельно. Мы же обратимся к другой задаче – отображение данных, хранящихся в базе данных СУБД Mysql.

Заметки начинающего администратора СУБД MySQL.

ПРО ЧТО «СТАТЬЯ»

ПРИСТУПАЯ.

ОГЛАВЛЕНИЕ

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

ОСНОВНЫЕ ПОНЯТИЯ И КОМПОНЕНТЫ MySQL.

каталог данных — содержит всю информацию, которая управляется сервером «mysqld» (базы данных, таблицы, файлы состояния). Место расположение «каталога данных» можно задать при запуске сервера с помощью опции:

-h|—datadir=path Path to the database root.

Определить текущие расположение «каталог данных» можно с помощью команды:

база данных — каждая БД представляет собой подкаталог в каталоге «каталога данных»

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

  • файл формы (описания)(.frm)
  • файл данных (.MYD)
  • файл индексов (.MYI)

файлы состояний MySQL

  • .pid PID процесса сервера
  • .err журнал ошибок
  • .log общий журнал
  • .nnn журнал обновлений (****)

.pid — PID процесса сервера. Изменяется с помощью опции —pid-file
.err — В этот журнал ведется запись события запуска и останова сервера, а также записи об ошибках.
.log — В этот журнал ведется запись событий подключения/отключения и информации о запросах. Изменяется с помощью опции -l|—log
.nnn — Журнал, в который ведется запись текста всех запросов, изменяющих содержимое или структуры таблиц. Задается помощью опции —log-bin | —log-update

Для включения «журнала обновлений» в новых версиях используется опция —log-bin. Журнал представляет собой бинарный файл и содержит всю информацию в более эффективном формате. В нем имеется информация о времени выполнения каждого обновляющего базу запроса. Просмотр этого бинарного журнала осуществляется с помощью утилиты «mysqlbinlog».

ОСНОВНЫЕ ПРОГРАММЫ И УТИЛИТЫ MySQL.

mysqld собственно сам сервер/демон MySQL.
mysql клиент для работы с сервером MySQL.
mysqladmin программа для выполнения административных функций.
myisamchk программа для проверки и восстановления MyISAM таблиц.
mysqldump консольный клиент для создания «дампов» или резервных копии БД, таблиц и хранимых данных.
perror по номеру ошибки выводит на экран описание этой ошибки.
mysqld_safe скрипт для запуска mysqld в системах UNIX.

Перечень некоторых идущих в дистрибутиве утилит можно найти в «man mysql» в топике «SEE ALSO».

Полезные команды/запросы программы клиента mysql.

Мой список стандартных запросов/команд, которыми мне часто приходилось пользоваться при работе с «mysql».

SHOW DATABASES; выводит список всех БД обслуживаемых сервером. Аналогичную информацию можно получить, запустив утилиту «mysqlshow».
USE [имя_БД] делает базу данных [имя_БД] «текущей» (активной).
SHOW TABLES; выводит список всех таблиц в «текущей» БД Аналогичную информацию можно получить, запустив утилиту «mysqlshow [имя_БД]».
DESCRIBE [имя_таблицы]; выводит описание таблицы [имя_таблицы] (имена столбцов, типы данных, и т.п). Аналогичную информацию можно получить, запустив утилиту «mysqlshow [имя_БД] [имя_таблицы]»
CREATE DATABASE [имя_БД]; создает БД с именем [имя_БД]
SELECT DATABASE(); выводит текущую БД
SELECT USER(); выводит имя (username) текущего пользователя
SELECT VERSION(); выводит информацию о версии сервера «mysqld»
TRUNCATE TABLE [имя_таблицы]; удаляет из таблицы [имя_таблицы] все строки
SELECT выбирает и возвращает строки из заданных таблиц, но лучше ознакомиться со всеми возможностями этого запроса в документации MySQL.

Более подробное описание синтаксиса и назначения операторов можно найти в документации устанавливаемой вместе с пакетом mysql. FreeBSD чтобы найти все установленные файлы какого либо пакета, можно воспользоваться командой:

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

ЗАПУСК И ОСТАНОВ СЕРВЕРА.

Запуск демона mysqld при старте системы.

Запуск/останов/перезапуск демона mysqld вручную.

При таком запуске «mysqld_safe» запустить «mysqld» с параметрами по умолчанию (большая часть, которых зависит от того, как и куда устанавливался «mysqld» и от опций указанных в конфигурационном файле «my.cnf»).
Но можно самому передать нужные опции «mysqld», указав эти опции в качестве параметров «mysqld_safe». Список возможных опций можно посмотреть «man mysqld» и «man mysqld_safe» если опции нельзя передать с помощью параметров, то их можно указать в конфигурационном файле «my.cnf».

Список полезных опций, которыми мне приходилось часто пользоваться при работе с «mysqld_safe»:

—defaults-file путь и имя конфигурационного файла «my.conf».
—ledir где будет искаться исполняемый файл «mysqld»
—basedir путь до установочной директории «mysqld», обычно все «относительные» пути будут определяться от этой директории.
—user имя пользователя под которым будет работать демон «mysqld».
—datadir путь к «каталогу данных».
—pid-file путь и имя PID файла.
—log куда протоколировать события.
—err-log куда протоколировать ошибки (man mysqld_safe)

РАБОТА С УЧЕТНЫМИ ЗАПИСЯМИ ПОЛЬЗОВАТЕЛЕЙ MySQL.

Идентификация и права доступа.

MySQL идентификация осуществляется по имени «хоста» и имени «пользователя», а проверка прав доступа к данным осуществляется два этапа:

  1. сервер проверяет, разрешено ли пользователю вообще подключаться к «mysqld» демону.
  2. если 1-й этап прошел успешно, то сервер начинает, проверяет каждый «запрос» пользователя на наличие привилегий для выполнения этого «запроса».

За выполнение 1-го этапа отвечает таблица «user» в БД «mysql», т.е чтобы пользователь смог подключиться к серверу «mysqld» для этого пользователя должна существовать соответствующая запись в таблице «user». Выполнение 2-го этапа осуществляется с помощью БД «mysql» таблиц «user», «db» и «host», «tables_priv», «columns_priv».

Таблицы «user», «db» и «host», «tables_priv», «columns_priv» организуют привилегии четырех уровней.
Глобальный уровень:
Глобальные привилегии применяются ко всем базам данных на указанном сервере. Эти привилегии хранятся в таблице «user».
Уровень базы данных:
Привилегии базы данных применяются ко всем таблицам указанной базы данных. Эти привилегии хранятся в таблицах «db» и «host».
Уровень таблицы:
Привилегии таблицы применяются ко всем столбцам указанной таблицы. Эти привилегии хранятся в таблице «tables_priv».
Уровень столбца:
Привилегии столбца применяются к отдельным столбцам указанной таблицы. Эти привилегии хранятся в таблице «columns_priv».

Во всех таблицах «user», «db» и «host», «tables_priv», «columns_priv» поля можно условно разделить на два типа:

  1. поля контекста.
  2. поля привилегий.

Поля контекста определяют область действия каждой из записей в таблицах, т.е. контекст, к которому имеет отношение та или иная запись.
Для таблицы «user» контекстные поля следующие: Host,User,Password
Для таблицы «db» контекстные поля следующие: Host,Db,User
Для таблицы «host» контекстные поля следующие: Host, Db
Для таблицы «tables_priv» контекстные поля следующие: Host,Db,User,Table_name
Для таблицы «columns_priv» контекстные поля следующие: Host,Db,User,Table_name, Column_name

Для более понятного понимания значения «контекста» рассмотрим следующий пример: Допустим в БД «mysql» в таблице «tables_priv» есть следующая запись.

Host Db User Table_name Grantor Timestamp Table_priv Column_priv
% testdb User1 testtb root@localhost YYY-MM-DD hh:mm:ss Select

Контекстные поля для таблицы «tables_priv» следующие «Host», «Db», «User», «Table_name» — и так расшифровываем запись исходя из контекста:

С любого компьютера «%» к БД «testdb» содержащей таблицу «testtb&raquo ; пользователю с учетной записью «user1» выдана привилегия «уровня таблицы» , которая разрешает пользователю «user1» выполнять запрос «SELECT» .

Поля привилегий определяют привилегии пользователей относительно контекста. Ниже представлена «таблица 1» возможных привилегий с кратким описанием назначений этих привилегий, с указанием типа привилегии для оператора GRANT/REVOKE и уровнем на, котором привилегия существует.

таблица 1.

название привилегии для операторов GRANT/REVOKE Название столбца/значения уровень глобальный уровень базы данных уровень таблиц уровень столбцов привилегия влияет на: описание
SELECT Select_priv X X X X tables без комментариев
INSERT Insert_priv X X X X tables без комментариев
UPDATE Update_priv X X X X tables без комментариев
DELETE Delete_priv X X X tables без комментариев
CREATE Create_priv X X X databases, tables, or indexes Привилегия CREATE позволяет создавать БД, таблицы
DROP Drop_priv X X X databases, tables, or indexes Привилегия DROP позволяет удалять БД, таблицы
RELOAD Reload_priv X server administration Позволяет выполнять reload, refresh, flush-privileges, flush-hosts, flush-logs, and flush-tables в команде mysqladmin
SHUTDOWN Shutdown_priv X server administration Позволяет выполнять опцию shutdown в команде mysqladmin
PROCESS Process_priv X server administration Привилегия PROCESS может быть использована для просмотра открытого текста запросов выполняющихся в данный момент, включая запросы на установку или изменение паролей.
FILE File_priv X file access on server host Привилегия FILE дает вам право читать и записывать файлы на сервере с помощью операторов LOAD DATA INFILE и SELECT . INTO OUTFILE. Любой пользователь, которому предоставлена такая привилегия, имеет право прочитать или записать любой файл, который может прочитать или записать сервер MySQL. Пользователь также может прочитать любой файл в каталоге текущей базы данных. Однако существующие файлы перезаписывать нельзя.
GRANT Grant_priv X X X databases, tables, or stored routines Привилегия GRANT позволяет пользователям передавать свои привилегии другим пользователям. Два пользователя с неодинаковыми привилегиями, имея привилегию GRANT, способны объединить свои привилегии
REFERENCES References_priv X X X X unused Не используется
INDEX Index_priv X X X tables Привилегия ALTER может быть использована для переименования таблиц и разрушения таким образом всей системы привилегий.
ALTER Alter_priv X X X tables Привилегия ALTER может быть использована для переименования таблиц и разрушения таким образом всей системы привилегий.
SHOW DATABASES Show_db_priv X server administration Привилегия SHOW DATABASES разрешает пользователям (account) просматривать имена баз данных используя оператор SHOW DATABASE.
SUPER Super_priv X server administration Позволяет использовать kill в mysqladmin
CREATE TEMPORARY TABLES Create_tmp_table_priv X X server administration Привилегия CREATE TEMPORARY TABLES разрешает использование слова TEMPORARY в операторе CREATE TABLE.
LOCK TABLES Lock_tables_priv X X server administration Позволяет явно использовать оператор LOCK TABLES для блокировки таблиц к которым есть привилегия SELECT.
EXECUTE Execute_priv X X stored routines
REPLICATION SLAVE Repl_slave_priv X server administration
REPLICATION CLIENT Repl_client_priv X server administration Привилегия REPLICATION CLIENT разрешает использовать операторы SHOW MASTER STATUS и SHOW SLAVE STATUS.
CREATE VIEW Create_view_priv X X views
SHOW VIEW Show_view_priv X X views
CREATE ROUTINE Create_routine_priv X X stored routines
ALTER ROUTINE Alter_routine_priv X X stored routines
CREATE USER Create_user_priv X server administration
Мастер Йода рекомендует:  Знакомство с созданием изображений на чистом CSS. Часть первая. Для начинающих

Так как в таблицах user,host,db поля привилегий имеют тип ENUM(‘N’,’Y’) т.е значение каждого поля может быть или «Y» или «N», а в таблицах tables_priv, columns_priv поля привилегий имеют тип SET то столбец «название столбца/значения» в «таблице 1» содержит просто название столбца для таблиц с типом поля ENUM, а для таблиц с типом поля SET значение, которое может содержать это поле.

Создание MySQL пользователей и назначение привилегий.

Создавать/удалять пользователей MySQL можно используя, операторы CREATE USER, DROP USER: http://dev.mysql.com/doc/refman/5.0/en/adding-users.html
http://dev.mysql.com/doc/refman/4.0/ru/adding-users.html

Назначать/отнимать привилегии лучше используя, операторы GRANT и REVOKE. Пример установки привилегий:

Где:
[тип_привилегии] — это устанавливаемые привилегии (см. таблица 1, 1-ый столбец), но также существует дополнительная привилегия «ALL» при указании, которой пользователю устанавливаются сразу все привилегии за исключением привилегии «GRANT OPTION»

[уровень_привилегий] — может быть следующим:
глобальный уровень — задается как *.*;
уровень базы данных — задается как [имя_БД].*;
уровень таблицы — задается как [имя_БД].[имя_таблицы];
уровень столбца — показано на примере ниже:

[имя_пользователя] — возможен следующий синтаксис для имени пользователя:
«username@hostname» при использовании такого синтаксиса пользователь «username» сможет подключаться к серверу mysqld только с компьютера с именем «hostname». Возможно, также при задании имени компьютера использовать символ «%», который означает «любой». В примере выше, создается пользователь «newuser» (если такого пользователя до этого не существовала), которому разрешается подключаться с любого компьютера домена «firma.lan» и присваиваются привилегии «глобального уровня» — (*.*). Пароль для входа назначается «qwerty».

Поиск разрешения привилегии идет следующим образом:
«use» => «db» & «host» => «tables_priv» => «columns_priv»
или на языке алгебры логики:
«user» OR («db» AND «host») OR «tables_priv» OR «columns_priv»

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

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

Сменить пароль можно с помощь оператора SET PASSWORD (***):
Первый строчка меняет пароль текущему пользователю, а вторая пользователю с именем «user».
Пример:

СОЗДАНИЕ РЕЗЕРВНОЙ КОПИИ БАЗ ДАННЫХ (БД).

Для создания копии БД можно использовать утилиту «mysqldump» или перл скрипт «mysqlhotcopy».

mysqldump

«mysqldump» — консольный клиент для «бэкапа» или создания «дампов» БД MySQL, таблиц или данных находящихся в этих таблицах. «Дамп» помещается в текстовый файл и выглядит как набор операторов MySQL необходимых для нового воссоздания БД. Синтаксис можно посмотреть «man mysqldump». Часто используемы и наиболее полезные опции (ИМХО):

—opt аккумулирует в себя сразу несколько опций:
—add-drop-table
—add-locks
—all
—extended-insert
—quick
—lock-tables
В документации рекомендуется использовать опцию «—opt» при создание копий БД объясняется это тем, что в этой опции содержится опция «—quick» без которой вся выборка в начале загружается в память, а это в свою очередь может создать проблемы при выборки из больших БД.
-B | —databases создает «дамп» нескольких БД, имена БД задаются виде аргументов
Пример:

создаст «дамп» БД mysql и test.

-A | —all-databases создает «дамп» всех БД тоже самое, что и опция -B, в которой указаны имена всех БД.
-T | —tab=[путь] для каждой заданной таблицы создает файл a table_name.sql, содержащий SQL CREATE команды для создания таблицы, и файл table_name.txt с данными таблицы и размещает их в [путь]. Работает только если «mysqldump» запущен на тойже машине, что и mysqld.
-v | —verbose в описание не нуждается
-F | —flush-logs (****) сброс данных журнала из буфера MySQL-сервера перед началом выполнения «дампа» в файл «журнала обновлений». По умолчанию эта опция сбрасывает данные буфера в журнал обновлений для каждой БД, для которой делается «дамп» тем самым если мы делаем «дамп» для N БД, то будет создано N файлов «журнала обновлений». Если нужно избежать создания для каждой БД отдельного сброса данных «журнала обновлений» то опцию «-F» нужно использовать совместно с опцией «—lock-all-tables» тогда при сбросе буфера для всех таблиц будет использован только один файл «журнал обновлений». Сброс данных в файл «журнала обновлений» обозначает, что будет создан новый файл «журнала обновлений» с новым именем [имя_файл.nnnnnn] где: nnnnnn это порядковый номер «журнала обновлений».

Также новый файл «журнала обновлений» создается:

  1. при каждом перезапуске сервера
  2. при выполнении команды «flush-logs»
  3. при достижение размера файла >= «max_binlog_size»
—ignore-table=[bd_name].[tb_name] исключить из «дампа» таблицу с именем [tb_name], чтобы исключить несколько таблиц нужно для каждой таблицы задавать опцию —ignore-table
-x | —lock-all-tables блокирует все таблицы всех БД. Это осуществляется с помощью установки «global read lock» во время выполнения всего «дампа». Автоматически выключает опции «—single-transaction» и «—lock-tables».
—add-drop-table эта опция означает, что в созданном «дамп» — файле будет обязательно присутствовать оператор «drop table» перед каждой восстанавливаемой таблицей. На понятном: если восстанавливаемая из «дампа» таблица уже существует в БД то перед началом восстановления она будет удалена из БД и создана заново операторами из «дамп» файла.
—add-locks перед созданием «дампа» таблицы над этой таблицей выполняется оператор LOCK TABLES по окончанию создания «дампа» выполняется оператор UNLOCK TABLE.
—all в документации написано «Включает все опции создания объектов, специфичные для MySQL», что скрывается за этой фразой я не знаю :(.
—extended-insert эта опция позволяет использовать более быстрый режим для оператора INSERT, который заключается в том, что будет использован оператор INSERT с многострочным синтаксисом. Почему будет быстрее можно почитать здесь: http://dev.mysql.com/doc/mysql/ru/insert-speed.html
—quick указав эту опцию, запросы не «буферизируются», а напрямую посылаются на STDUOT. Я это понимаю так:
Если бы использовалась «буферизация» то запросы бы помешались в «буфер» до тех пор, пока буфер не будет заполнен до нужного состояния и только, потом это все выводиться на STDOUT. Соответственно буферизация замедляет процесс вывода данных и загружает память при больших БД. Поэтому опция «—quick» и повышает скорость выполнения запросов за счет отключения «буфера».
—lock-tables блокирует все таблицы блокировкой READ LOCAL. Различие между READ LOCAL и READ состоит в том, что READ LOCAL позволяет выполнять неконфликтующие операторы INSERT во время существования блокировки.

После прочтения «манулов» для себя я решил запускать «mysqldump» со следующими опциями:

Опцию «-F» я использую, чтобы во время «дампа» создавался новый файл «журнала обновлений» это позволит мне в будущем, в случае сбоя, при восстановлении данных использовать только файлы текущего «журнала обновлений», который хранит протокол последних изменений БД после создания её последний резервной копии утилитой «mysqldump».

Опцию «-x» для того чтобы создавался только один новый файл «журнала обновлений», если не поставить эту опцию то «журналы обновлений» будут создаваться для каждой БД, для которой делается «дамп», а это неудобно потому, что всё равно протоколирование изменений ведется только в один файл «журнала обновлений». (ИМХО)

Опция «-A» («дамп» всех БД) почти по той же причине, что и опцию «-x» Я не знаю как при восстановлении с использованием «журнала обновлений» выделять из этого журнала изменения только для нужной БД.

Опция «—ignore-table». Если в моей БД есть таблицы, которые не важны для меня, а исключение их из «дампа» увеличит производительность и уменьшит время создания «дампа». То я исключаю такие таблицы с помощью этой опции. Также для «неважных» БД данных лучше отключить протоколирование в «журнал обновлений». Для этого можно использовать опции «binlog-do-db» или «binlog-ignore-db».

Опцию «—opt» использую потому, что её рекомендуют использовать 🙂

Восстановление БД из «дамп» файлов

ОБНАРУЖЕНИЕ, ИСПРАВЛЕНИЕ ОШИБОК И ВОССТАНОВЛЕНИЕ БД ПОСЛЕ СБОЯ.

Процедура обнаружения и исправления ошибок состоит из следующих этапов:

  1. Проверка таблиц на наличие ошибок. Если ошибки в таблице отсутствуют, процедура завершается. В любом другом случае возникает необходимость их устранения.
  2. Перед началом исправления создается копия файлов таблиц на случай негативного развития событий.
  3. Попытка исправления таблицы
  4. Если попытка оказывается неудачной, остается лишь восстанавливать базу данных их архива («дампа») и если есть, то из «журналов обновлений».

Для использования пункта (4) (восстановление с минимальными потерями) нужно обязательно включить журнал обновлений (опция «—log-bin») и систематически создавать резервные копии БД утилитой «mysqldump» или «mysqlhotcopy».

Список номеров — описаний ошибок, по которым можно предположить, что таблица повреждена:
perror 126 127 132 134 135 136 141 144 145

Перед проверкой и восстановлением таблиц при работающем сервере «mysqld» следует обновить «кеш-память» таблиц командой «mysqladmin flush-tables»

Проверять и восстанавливать MyISAM таблицы можно с помощью утилиты «myisamchk», а также можно использовать операторы CHECK и REPAIR.

Синтаксис «myisamchk» можно посмотреть командой: «myisamchk —help | less » вкратце это выглядит так:

Проверка таблиц на наличие ошибок.

Где «список_опций_проверки»:

-c | —check обычная проверка (по умолчанию)
-e | —extend-check более тщательная проверка
-m | —medium-check детальная проверка (самая долгая)

Можно проверить все таблицы во всех базах данных, если задать шаблон вместе с путем к каталогу данных MySQL:

Исправление таблиц содержащих ошибки.

Для исправления ошибок можно пользоваться следующими командами:

восстановление без модификации файла данных (.MYD);

если проблема осталась не решённой то:

может исправить большинство проблем за исключением несовпадения ключей;

если проблема осталась не решённой то:

использует старый метод восстановления, медленней чем «—recover», но может исправить некоторые случаи, в которых не помогает опция «—recover».

Восстановление INDEX файла таблицы (*.MYI).

  1. перейти в каталог БД, содержащий файлы поврежденной таблицы.
  2. скопировать файл данных таблицы (*.MYD) в безопасное место.
  3. запустить «mysql» и выполнить следующие команды:

Или перезапустить демон «mysqld».

разбор пункта #3,6.
Оператор «use [имя_БД]» — делает активной БД [имя_БД].
Оператор «SET AUTOCOMMIT=1» — команда не обязательная (её можно упустить), устанавливает значение переменной «AUTOCOMMIT» равным единице (*6*).
Оператор «TRUNCATE TABLE» — опустошает полностью таблицу. Этот оператор похож на «DELETE», который удаляет все строки в таблице.

Оператор «FLUSH TABLE» принудительно закрывает открытую таблицу, также сбрасывается кэш запросов. Для принудительного закрытия всех таблиц можно использовать оператор «FLUSH TABLES».

разбор пункта #5
«myisamchk -r -q [имя_таблицы]» запускает быстрое восстановление таблицы («-q» ключ означает быстрое восстановление, при этом исправляются только файл индексов, а файл данных не изменяется)

Восстановление файла описания таблицы (*.frm).

Работа с блокировками таблиц во время ремонта.

Сервер MySQL использует два вида блокировок:

  1. внутренняя блокировка
  2. внешняя блокировка (на уровне файловой системы)

1-я применяется чтобы избежать взаимного влияния запросов клиентов (пример: не позволяет «SELECT» одного клиента выдать не правильные данные из-за одновременной запроса «UPDATE» другого клиента).
2-я не позволяет внешним программам изменять файлы таблиц, пока с ними работает сервер «mysqld».

От того включена ли внешняя блокировка будет зависеть, как запускать утилиту «myisamchk» (если нельзя включить (2) то нужно использовать (1)). Просмотреть состояние внешней блокировки можно с помощью команды «SHOW VARIABLES» переменная «skip_external_locking» или «skip_locking» будет содержать значение «ON» или «OFF». Значение «ON» означает, что внешняя блокировка отключена, значение «OFF» наоборот. Для того чтобы изменить значение переменной «skip_external_locking» нужно запустить сервер «mysqld» с соответствующей опцией (по умолчанию во FreeBSD 5.2 значение «ON»).

Если значение «skip_external_locking» равно «ON» (внешняя блокировка отключена) то для выполнения проверки или восстановления с помощью утилиты «myisamchk» лучше приостановить работу сервера «mysqld» или воспользоваться внутренним механизмом блокировки (закрыть на время доступ к проверяемым таблицам с помощью «mysql» команды «LOCK TABLES»). Для того чтобы воспользоваться внутренним механизмом блокировки для команды «myisamchk» следует:

  1. запустить «mysql» и выполнить команду «LOCK TABLES» для нужной (исправляемой) таблицы
  2. не завершая работы «mysql» запустить утилиту «myisamchk» с нужными опциями проверки/исправления
  3. по окончанию работы утилиты «myisamchk» нужно вернуться к сессии «mysql» выполнить команду «FLUSH TABLES» и снять блокировку «UNLOCK TABLES»

Механизм блокировки может отличаться в зависимости от режима работы утилиты ясно, что если утилита »myisamchk» запущена только в режиме проверки данных то клиентам можно оставить право на чтения таблицы но, а если утилита будет восстанавливать таблицу то клиентам нужно запретить что либо изменять в таблице.
использовать механизм блокировки «LOCK» лучше в следующей последовательности:

  1. «LOCK TABLE [имя_таблицы] [READ | WRITE]». Если установлено «READ» для некоторой таблицы, то только этот поток (и все другие потоки) могут читать из данной таблицы. Если для некоторой таблицы установлена блокировка «WRITE», тогда только этот поток, содержащий блокировку, может осуществлять операции чтения «READ» и записи «WRITE» заблокированной таблицей. Остальные потоки блокируются. То есть простыми словами если нам нужно просто проверить таблицу, то устанавливаем блокировку «READ», но а если исправить, то устанавливаем «WRITE».
  2. после того, как закончили проверку/ремонт таблицы, выполняем команду «FLUSH TABLES» для того чтобы сбросить кэш таблиц.
  3. выполнить команду «UNLOCK TABLES» когда нужно будет снять блокировку.

СОЗДАНИЕ ПРОФИЛАКТИЧЕСКИХ МЕР И ПОДГОТОВКА К САМОМУ ХУДШЕМУ «СБОЮ».

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

  1. проверка таблиц при загрузке системы (полезно при сбоях питания)
  2. проверка таблиц по расписанию во время работы сервера «mysqld».
  3. создание резервных копий БД.


И конечно самым главным пунктом будет создание резервных копий БД по заданному расписанию (пункт 3).

Если включена опция «—log-bin | —log-update» то есть, включен «журнал обновлений» то все изменения БД записываются в этот журнал и его можно использовать совместно с созданной резервной копией БД чтобы уменьшить количество потерянных «записей».

Было бы не плохо, если после создания каждой резервной копии БД «журнал обновлений» обнулялся — это сделает восстановление более ясным так, как в случае сбоя нам для воссоздания БД с минимальным числом потерянных записей необходимо иметь только два слагаемых:

«последний_журнал_обновлений» + «последняя_резервная_копия_БД» = «БД_с_минимальным_количеством_потерянных_записей»

ИМХО лучше с помощью опций «—binlog-do-db», «—binlog-ignore-db» исключить из протоколирования БД для, которых восстановление не важно.

Вот как я решил организовать три пункта своей стратегии:

пункт #1 «проверка таблиц при загрузке системы»
Так как для загрузки демона MySQL я использую «rc.d» систему FreeBSD, то логично было бы, встроить проверку таблиц в скрипт «/etc/rc.d/mysql» это также даcт возможность вынести нужные опции проверки в главный конфигурационный файл FreeBSD — /etc/rc.conf.

Конечно, можно не встраивать проверку таблиц в стартовый скрипт «/etc/rc.d/mysql», помня то — что «/etc/rc.d/mysql» просто запускает другой скрипт «mysqld_safe», а тот в свою очередь уже имеет нужные строчки по проверке таблиц их только нужно найти и раскомментировать. Но мне хотелось, чтоб этой проверкой я мог управлять через «rc.conf».

Пример: моего «/etc/rc.d/mysql»: Красным шрифтом я выделил фрагменты кода, которые я добавил в скрипт «/etc/rc.d/mysql», который был создан при установке MySQL. Смысл моих строчек очень прост:

Строчки «29-32» это задание значений по умолчанию для опций, которые можно прописать в файле «/etc/rc.conf» и с их помощью управлять поведением «myisamchk».

mysql_myisamck
Может принимать значения YES или NO. Если значение YES то пред запуском «mysqld» будет проходить проверка всех таблиц в «каталоге данных».

mysql_myisamck_args
Строка аргументов, которые будут переданы утилите «myiasmchk». По умолчанию заданы аргументы «-s -C». «-s» — обозначает работу в «тихом» режиме то есть на STDOUT будет выводиться информация, только в случае если в таблицах будет найдена ошибка. «-С» обозначает, что проверка будет происходить только тех таблица в которых были изменения после последней проверки.

mysql_myisamck_mail_subj
Текстовая строка, которая будет темой в письме уведомлении, которое будет отправляться, в случае если в таблицах будет найдена ошибка.

mysql_myisamck_mail_toaddr
Адрес электронной почты, куда будет послано письмо уведомление.

Строка «33» задает путь к утилите «myisamchk».
Строки «56-67» собственно и есть код проверки таблиц и отправки письма в случае нахождения ошибок в таблицах.

пункт #2 «проверка таблиц по расписанию во время работы сервера»
Реализовать пункта #2 можно разными способами, лично я решил это сделать так:

  1. написать свой скрипт проверки таблиц.
  2. в «crontab» прописать расписание выполнения этого скрипта.

Первое уже почти сделано в «пункте #1» нужно только файл «/etc/rc.d/mysql» скопировать в другое место переименовать его и удалить не нужные строчки:

Пример: вот как у меня выглядит «/etc/mysqld/cronchmysql.sh»: Жирным шрифтом выделено то, что нужно/можно подправить под свои нужды. Что означают эти переменные описано в «пункте #1» за исключением переменной «mysql_dbdir» в ней задаётся путь к «каталогу данных».

Второе еще проще, чем первое придумываем расписание, по которому будет запускать проверку таблиц. У меня БД ночью почти не используются, поэтому я решил проверку по расписанию назначить один раз в неделю (суббота) в 4 часа утра. Для этого добавил в «crontab» следующею строчку:

Для редактирования «crontab» нужно использовать команду «crontab -e».

пункт #3 «создание резервных копий БД»
Для реализации пункта #3 я буду использовать описанную выше утилиту «mysqldump». и как для пункта #2.1 снова будет написан небольшой скрипт, который будет реализовывать следующие действия:

  1. вызывать «mysqldump» для создания копии всех баз данных с обязательным обнулением «журнала обновлений»
  2. созданная копия БД будет сжиматься с помощью «bzip2».
  3. администратору будет высылаться на электронную почту уведомление, содержащее отчет о выполненных действиях и дополнительную информацию.

Пример: вот как выглядит мой скприт для создания бэкапа «/etc/mysqld/cronbkmysql.sh». То что выделено жирным шрифтом эти переменные нужно подправить под свои конкретные настройки и нужды. Остается только снова придумать расписание выполнения скрипта и создать соответствующую запись в «crontab».

НАСТРОЙКА ОСНОВНЫХ ПАРАМЕТРОВ СЕРВЕРА.

Настраивать параметры работы сервера можно с помощью конфигурационного файла или опций командной строки. Для меня интересны только следующие опции/параметры:

—bind-address=IP
—port=#
—character-sets-dir=[path]
—chroot=[path]
—datadir=[path]
—log[=file]
—p >
—skip-name-resolve
—skip-networking
—socket=path
—user=[user_name]

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

Описание каждой этой опции есть в документации MySQL (http://dev.mysql.com/doc/refman/4.0/ru/command-line-options.html) поэтому я опишу только несколько опций, которые полезно использовать в частных случаях:

—skip-name-resolve
эту опцию полезно использовать, когда в сети существуют «проблемы» с DNS, при включении этой опции демон «mysqld» не будет преобразовывать IP адреса в их канонические имена. Для использования этой опции, нужно помнить, что в таблице «User» в поле «host» надо использовать IP адреса, а не DNS имена хостов.

—skip-networking
эту опцию полезно включать, если вы решили не предоставлять доступ по сети к базам данных. При включении этой опции соединиться сервером можно будет, только используя UNIX SOCKET.

Для меня удобней всего задавать параметры в конфигурационном файле (http://dev.mysql.com/doc/refman/4.0/ru/adding-users.html). Примеры стандартных конфигурационных файлов можно найти после установки MySQL на жестком диске:

РАБОТА НЕСКОЛЬКИХ СЕРВЕРОВ MySQL НА ОДНОМ КОМПЬЮТЕРЕ.

Иногда бывает нужно запустить несколько MySQL серверов на одно компьютере, например для тестирования новой версии MySQL или для экспериментов и т.п. Это просто сделать, используя утилиту «mysqld_safe» указав ей соответствующий конфигурационный файл в котором можно/нужно задать основные опции.

Пример:
Первый сервер читает конфигурационный файл /etc/mysqld3306.cnf, в котором в секции [mysqld] указано что: а с помощью опций «mysqld_safe» также установлен «каталог данных»/var/db/mysql.

Для запуска второго сервера создадим для этого сервера новый «каталог данных» используя скрипт «mysql_install_db», который устанавливается при установке сервера mysqld, новый конфигурационный файл «/etc/mysqld3307.cnf», и нового системного пользователя «mysql3307»:

Опция «—ldata=/var/db/mysql3307» задает место, где будет создан «каталог данных», опция «—user=mysql3307» задет имя пользователя которому будет разрешено работать с «каталогом данных». Пользователя «mysql3307» я создал с помощью /stand/sysinstall.

Затем нужно отредактировать файл /etc/mysqld3307.cnf изменить в секции [mysqld] строчки на: И запустить «mysqld_safe» со следующими опциями (*7*):

СОВЕТЫ ПО ПОВЫШЕНИЮ БЕЗОПАСНОСТИ MySQL.

Перед тем как начать перечислять советы приведу список полезных ссылок по безопасности MySQL:

Securing MySQL step-by-step: http://www.securityfocus.com/infocus/1726
Secure MySQL Database Design: http://www.securityfocus.com/infocus/1667
Securing Your MySQL Installation: http://www.kitebird.com/articles/ins-sec.html
и естественно нужно просмотреть http://dev.mysql.com/doc/refman/5.0/en/security.html

Советы:

  • Следить за последними обновлениями (заплатками) MySQL.
  • Ограничить с помощью брандмауэра, доступ по сети к серверу MySQL, разрешив доступ к серверу только с доверенных/нужных хостов.
  • Удалить из таблицы User «анонимного» пользователя.
  • Переименовать учетную запись root пользователя MySQL, во что нибудь другое и задать учетной записи root сложный пароль.
  • Для каждого web приложения требующего MySQL желательно создавать отдельную учетную запись.
  • Привилегии глобального уровня выдавать пользователям только в случае крайней необходимости.
  • Привязать доступ пользователей MySQL к БД только заранее определенных хостов (поле host в таблице User) и исключить использование пользователями пустых паролей.
  • Запускать демон «mysqld» под системной учетной записью обладающую минимальными правами (под FreeBSD демон «mysqld» по умолчанию запускается с правами пользователя «mysql»).
  • Запускать демон «mysqld» с опцией «—chroot» это позволит ограничить доступ к файлам, находящимися выше «chroot» директории для операторов «LOAD DATA INFILE» и «SELECT . INTO OUTFILE». (*9*)
  • Установить для «каталога данных» и «журналов» MySQL разрешения на доступ и просмотр только для пользователя, под которым работает демон «mysqld».
  • С большой осторожностью выдавайте пользователям привилегии «File_priv» (*9*), «Grant_priv» (*10*) и «Alter_priv»
  • Включить опцию «—skip-show-database».

ЧТО Я ЧИТАЛ ДЛЯ НАПИСАНИЯ ЭТОЙ ЗАМЕТКИ.

(*9*) Операторы «LOAD DATA INFILE» и «SELECT . INTO OUTFILE» опасны тем, что возможно выполнить следующий «ФИНТ»:

mysql#> create table foo( line blob );
mysql#> load data infile ‘/etc/passwd’ into table foo;
mysql#> select * from foo;
или
mysql#> select load_file(‘/etc/passwd’);

поэтому если эти операторы разрешены то необходимо их ограничить используя опцию «—chroot»

Writed by Krylov Stanislav aka Dragon
(November 2005)

SQL для начинающих

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

Что Вам потребуется?

SQL (Structured Query Language) язык специально разработанный для взаимодействия с системами управления баз данных, таких как MySQL, Oracle, Sqlite и прочие. Для выполнения SQL запросов в этой статье я советую Вам установить MySQL на локальный компьютер. Также я рекомендую использовать phpMyAdmin в качестве визуального интерфейса.

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

В денвере есть встроенная MySQL консоль. Ей мы и будем пользоваться.

Автор урока пользуется WAMP ом и все скриншоты и примеры будут из этой программы и ее MySQL консоли.

CREATE DATABASE: создание базы данных

Вот и наш первый запрос. Мы создадим нашу первую БД для дальнейшей работы.

Для начала, откройте MySQL консоль и залогиньтесь. Для WAMP пароль по умолчанию пустой. То есть ничего :). Для MAMP — «root». Для Денвера необходимо уточнить.

После логина введите следующую строку и нажмите Enter:

Заметьте, что точка с запятой (;) добавляется в конце запроса, так же как и в других языках.

Также команды в SQL чувствительны к регистру. Пишем их большими буквами.

Опци онально: Character Set и Collation

Если Вы хотите установить character set ( набор символов ) и collation ( сравнение ) можно написать следующую команду:

Тут находится список наборов символов, которые поддерживаются в MySQL.

SHOW DATABASES: выводит список всех БД

Эта команда используется для вывода всех имеющихся БД.

DROP DATABASE: удаление БД

Вы можете удалить существующую БД с помощью этого запроса.

Будьте осторожны с этой командой, так как она выполняется без предупреждения. Если в Вашей БД есть данные, они будут все удалены.

USE: Выбор БД

Технически это не запрос, а оператор и он не требует точки с запятой в конце.

Он сообщает MySQL выбрать БД для работы по умолчанию для текущей сессии. Теперь мы готовы создавать таблицы и делать прочие вещи с БД.

Что же такое таблица в БД?

Вы можете представить таблицу в БД в виде Excel файла.

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

CREATE TABLE: Создание таблицы

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

Следующий запрос создаст таблицу с 2-мя колонками.

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

Первая строка простая. Мы просто создаем таблицу с названием » users «. Далее в скобках, через запятую, идет список всех колонок. После каждого названия колонки у нас идут типы информации, такие как VARCHAR или DATE.

VARCHAR(20) означает, что колонка имеет тип строки и может быть максимум 20 символов в длину. DATE также тип информации, который используется для хранения дат в таком формате: » ГГГГ — ММ-ДД «.

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

INT делает 32 битный целый тип (например, числа). AUTO_INCREMENT автоматически генерирует новое значение >каждый раз, когда мы добавляем новые ряды информации. Это не обязательно, но делает весь процесс проще.

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

Давайте выполним запрос:

SHOW TABLES: показать все таблицы

Этот запрос позволяет получить список таблиц, которые находятся в БД.

EXPLAIN: Показать структуру таблиц

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

Колонки отображаются со всеми свойствами.

DROP TABLE: удалить таблицу

Также как и DROP DATABASES, этот запрос удаляет таблицу и ее содержание без предупреждения.

ALTER TABLE: изменить таблицу

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

(если Вы удалили таблицу в прошлом шаге, создайте ее снова для тестов)

Благодаря хорошей читабельности SQL, я думаю, что нет смысла ее подробно объяснять. Мы добавляем новую колонку » email » после » username «.

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

Восстановите только что удаленную колонку для дальнейших экспериментов.

ВНЕСЕНИЕ ИЗМЕНЕНИЯ В КОЛОНКУ


Иногда Вы можете захотеть внести изменения в свойства колонки, и Вам не надо ее полностью удалять для этого.

Этот запрос переименовал колонку пользователь в » user_name » и изменил ее тип с VARCHAR(20) на VARCHAR(30). Такое изменение не должны изменить данные в таблице.

INSERT: Добавление информации в таблицу

Давайте добавим некоторую информацию в таблицу используя следующий запрос.

Как Вы можете увидеть, VALUES () содержит список значений, разделенных запятыми. Все значения заключены в одинарные колонки. И значения должны быть в порядке колонок, которые были определены при создании таблицы.

Заметьте, что первое значение NULL для поля PRIMARY KEY под названием » user_id «. Мы делаем это для того, чтобы ID было сгенерировано автоматически, так как колонка имеет свойство AUTO_INCREMENT. Когда информация добавляется первый раз ID будет 1. Следующий ряд — 2, и так далее.

Есть еще один вариант запроса для добавления рядов.

В этот раз мы используем ключевое слово SET вместо VALUES, и у него нет скобок. Есть несколько нюансов:

— Колонку можно пропустить. К примеру, мы не присвоили значение для » user_id «, которое по умолчанию получит свое AUTO_INCREMENT значение. Если Вы пропустите колонку с типом VARCHAR, тогда будет добавлено пустая строка.

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

АЛЬТЕРНАТИВНЫЙ ВАРИАНТ 2

Вот еще вариант.

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

LAST_INSERT_ID()

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

NOW()

Теперь настало время показать, как Вы можете использовать функцию MySQL в запросах.

Функция NOW() выводит текущую дату. Так что Вы можете использовать ее для автоматического установления даты колонки на текущую при вставке нового ряда.

Заметьте, что мы получили 1 предупреждение, но не обращайте на него внимания. Причина этому то, что NOW() также служит для вывода временной информации.

SELECT: Чтение данных из таблицы

Если мы добавляем информацию в таблицу значит логично было бы научиться ее оттуда считывать. Именно в этом нам и поможет запрос SELECT.

Ниже представлен самый простой возможный запрос SELECT для чтения таблицы.

В этом случае звездочка (*) означает то, что мы запросили все поля из таблицы. Если Вы хотите только определенные колонки, запрос будет выглядеть так.

Условие WHERE

Чаще всего мы заинтересованы не во всех колонках, а только в некоторых. К примеру, давайте предположим, что нам необходимы только электронный адрес для пользователя » nettuts «.

WHERE позволяет устанавливать условия в запросе и делать подробные выборки.

Заметьте, что для равенства использоваться один знак равно (=), а не два, как в программировании.

Вы можете также использовать сравнения.

AND или OR могут быть использованы для объединения условий:

Заметьте, что числовые значения не должны находиться в кавычках.

IN()

Это полезно для выборки по нескольким значениям

LIKE

Позволяет делать «wildcard» запросы

Значок % используется в качестве «wildcard». То есть на его месте может быть что-угодно.

Условие ORDER BY

Если Вы хотите получить результат в упорядоченном виде по какому либо критерию

Порядок по умолчанию ASC ( от меньшего к большему ). Для обратного используется DESC.

Вы можете ограничить количество полученных результатов.

LIMIT 2 берет только 2 первых результата. LIMIT 1 OFFSET 2 получает 1 результат после первых 2-х. LIMIT 2, 1 означает тоже самое (только обратите внимание сначала идет offset а потом limit ).

UPDATE: Внести изменения в информацию в таблице

Этот запрос используется для изменения информации в таблице.

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

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

DELETE: Удаление информации из таблицы

Также как и UPDATE, этот запрос используется с WHERE:

Для удаления содержания таблицы можно сделать просто так:

Но лучше использовать TRUNCATE

Кроме удаления этот запрос также сбрасывает значения AUTO_INCREMENT и при добавлении рядов снова, отсчет начнется с нуля. DELETE такого не делает и отсчет продолжается.

Отключение Строчных Значений и Специальных Слов

Некоторые символы необходимо отключать ( escape ), или же могут быть проблемы.

Для этого используется задний слеш (\).

Поскольку в MySQL есть много специальных слов ( SELECT или UPDATE ), чтобы избежать ошибок при их использовании необходимо использовать кавычки. Но не обычные кавычки, а вот такие (`) .

То есть Вам необходимо будет добавить колонку с именем » delete «, это необходимо сделать так:

Заключение

Спасибо, что дочитали до конца. Надеюсь, Вам эта статья была полезна. Это еще не конец! Будет продолжение :).

Данный урок подготовлен для вас командой сайта ruseller.com
Источник урока: www.nettuts.com
Перевел: Максим Шкурупий
Урок создан: 10 Марта 2010
Просмотров: 390754
Правила перепечатки

5 последних уроков рубрики «Разное»

Как разместить свой сайт на хостинге? Правильно выбранный хороший хостинг — это будущее Ваших сайтов

Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.

Разработка веб-сайтов с помощью онлайн платформы Wrike

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

20 ресурсов для прототипирования

Подборка из нескольких десятков ресурсов для создания мокапов и прототипов.

Топ 10 бесплатных хостингов

Небольшая подборка провайдеров бесплатного хостинга с подробным описанием.

Быстрая заметка: массовый UPDATE в MySQL

Ни для кого не секрет как в MySQL реализовать массовый INSERT, а вот с UPDATE-ом могут возникнуть сложности. Чтобы не прибегать к манипуляциям события ON_DUPLICATE можно воспользоваться специальной конструкцией CASE … WHEN … THEN.

Работа с MySQL

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

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

Основными понятиями, с которыми следует ознакомиться на данном этапе, являются:

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

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

Доступ к MySQL из командной строки

Работать с MySQL можно тремя основными способами: используя командную строку, используя веб-интерфейс наподобие phpMyAdmin и используя такой язык программирования, как PHP. Третий из перечисленных способов будет рассмотрен в последующих статьях, а сейчас давайте рассмотрим первые два способа.

Если у вас в соответствии с инструкциями, изложенными в статье Установка и настройка OpenServer, установлен WAMP-сервер OpenServer, то доступ к исполняемой программе MySQL можно получить из следующего каталога:

Вместо MySQL-5.7-x64 нужно подставить версию, указанную в настройках OpenServer во вкладке «Модули».

Нужно открыть программу «Командная строка» и перейти в этот каталог. Делается это при помощи команды cd ПУТЬ_К_НУЖНОЙ_ПАПКЕ:

После этого нужно запустить программу mysql.exe в этом каталоге, передав её специальный параметр. Для этого в командной строке теперь нужно выполнить команду:

В результате запустится MySQL-клиент. Он подключён к MySQL-серверу, который был запущен при старте OpenServer-a.

Если это не приведет к желаемому результату и будет выдано сообщение об ошибке подключения к серверу MySQL «Can’t connect to MySQL server on ‘localhost’», убедитесь, что OpenServer запущен и в модулях указан MySQL.

Параметр -u расшифровывается как user. То есть это флажок для указания пользователя, под которым нужно подключиться к серверу. root — это самый главный пользователь в MySQL. Он создаётся при установке сервера и по умолчанию у него нет пароля.

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

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

Работа с MySQL через phpMyAdmin

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

Тут нас встретит вот такое красивое окошечко для входа в систему.

Также как и в случае с консольным приложением указываем пользователя root и оставляем пустым пароль. Жмём «войти».

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

А сейчас давайте нажмём на вкладку SQL и перейдём в окно, где можно напрямую писать запросы к СУБД MySQL, как это было бы в консоли:

В открывшемся окне введите всё тот же запрос:

Нажимаем кнопку «вперёд» и видим тот же результат, что и в случае с консольным приложением.


Основные понятия языка SQL

По словам Эндрю Тейлора (Andrew Taylor), разработавшего язык SQL, название этого языка не является сокращением от Structured Query Language (или от чего-то подобного), хотя многие считают, что так оно и есть. Язык SQL лежит в основе более строгого и более общего метода хранения данных по сравнению с предыдущим стандартом организации баз данных в стиле DBM, который основан на использовании плоских файлов.

Язык SQL определен стандартами ANSI (American National Standards Institute) и ECMA (European Computer Manufacturer’s Association); обе эти организации по стандартизации являются международно признанными. Но следует учитывать, что в общих рекомендациях стандартов SQL наблюдаются заметные различия, касающиеся программных продуктов коммерческих компаний, с одной стороны, и организаций, занимающихся разработкой баз данных с открытым исходным кодом, с другой. Например, за последние несколько лет наблюдалось стремительное развитие так называемых объектно-реляционных баз данных, а также программных продуктов SQL, специально предназначенных для рынка веб. Перечень баз данных, которые могут применяться в сочетании с системой PHP, чрезвычайно велик, поэтому при выборе наиболее подходящей базы данных необходимо руководствоваться определенными принципами.

Основной принцип состоит в том, что нужно прежде всего учитывать собственные потребности разработки или по крайней мере твердо знать, какой цели требуется достичь. Пытаясь ознакомиться с другими рекомендациями, вы встретите массу весьма убедительных сообщении с такими доводами, что буквально «нельзя обойтись» без некоторых развитых средств базы данных (таких как триггеры или хранимые процедуры) и любой программный продукт с поддержкой SQL, в котором отсутствуют эти средства, вряд ли заслуживает права называться продуктом SQL. Воспринимайте эти суждения с большой долей сомнения. Гораздо лучше самому составить список требуемых характеристик в порядке значимости, а затем заняться поиском программного продукта, который в наибольшей степени соответствует вашим требованиям.

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

По существу, база данных SQL имеет очень простую логическую структуру. Каждая конкретная инсталляция программного обеспечения SQL обычно может состоять из нескольких баз данных. Например, одна БД может применяться для хранения данных о заказчиках, а другая — содержать данные о товарах. (Возникает определенная сложность, связанная с тем, что сам сервер SQL и коллекции поддерживаемых этим сервером таблиц обычно принято обозначать общим термином база данных.) Каждая база данных содержит несколько таблиц, каждая таблица состоит из тщательно определенных столбцов, а каждая позиция в таблице может рассматриваться как внесенная в таблицу запись, или строка.

Любой сервер SQL поддерживает четыре так называемых оператора манипулирования данными, и в целом эти операторы лежат в основе подавляющего большинства операций, выполняемых с реляционной базой данных. Этими четырьмя основными операторами базы данных являются SELECT, INSERT, UPDATE и DELETE. Операторы SQL, называемые также командами — очень удобны и позволяют выполнять практически все необходимые действия в базе данных.

Важной особенностью указанных четырех операторов SQL является то, что они позволяют манипулировать только значениями, хранящимися в базе данных, но не воздействуют на структуру самой базы данных. Иными словами, команды на основе этих операторов могут использоваться, например, для ввода данных, а не для создания базы данных; с помощью таких команд можно удалить из базы данных любой фрагмент данных, но сама «оболочка» останется нетронутой, поэтому, в частности, нельзя присвоить другой базе данных, работающей под управлением того же сервера, имя существующей базы данных. Для того чтобы добавить или удалить столбцы, уничтожить целую базу данных, не оставив и следа, или создать новую базу данных, необходимо применить другие команды, такие как DROP, ALTER и CREATE.

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

MySQL — это просто!

Если Вы видите такие термины как: MySQL, SQL, база данных, БД, database, DB, таблица, СУБД, то знайте, речь идет о БАЗЕ ДАННЫХ!
Конечно все это немного разные вещи, но если Вы в этом не разбираетесь, то не забивайте себе голову, в конце статьи расскажем кому интересно.

База Данных, как Вы уже могли догадаться, это ящик с информацией. В этот ящик информацию можно положить, удалить или найти. Любой сайт использует сервер для хранения данных, будь то список юзеров, комментарии посетителей, или счетчик посещений.
Но мы используем Базу Данных для сервера кс1.6. Для чего?
Например для статистики игроков, или список банов, админки и пароли тоже храним в базе.

Так почему бы не хранить все эти данные на сервере в файлах? Зачем нам какой то SQL?
Все очень просто!
Чтобы серверу получить необходимую информацию из файла, нужно прочитать сначала 1-ую строку, посмотреть есть в ней то что нужно, если нет то, 2-ую и тд, пока не найдет то что нужно.
MySQL же работает так: друг, я знаю где это лежит! Держи!

Круто да? Почему MySQL такая быстрая штука? Правильней сказать, что база данных это не ящик, а таблица. Смотрите как выглядит например статистика игроков:

Или вот так выглядит хранение банов:

Плагин посылает запрос в базу, типа: проверь, есть ли игрок со стим айди STEAM_0:0_123456789 в таблице `amx_bans` с не истекшим сроком бана
MySQL говорит: да, есть, вот такая причина бана и истечет срок через 10 дней. Плагин в свою очередь не пускает игрока на сервер.
Все данные в каждой колонке таблицы имеет свой тип: INT целое число, varchar текст, есть еще BOOL, FLOAT и куча других типов, их значения занимают мало места, благодаря «индексам» таблица сортируется и еще тонна всяких штук, благодаря которым из миллиона строк в таблице с банами, mysql найдет игрока меньше чем за секунду.
В файлах, на сервере кс, обычно хранят мало информации, потому что сервак просто зависнет если это будет не так. Помните да, что csstats.dat может очиститься и статистика игроков обнулится, этого не произойдет если использовать CsStats MySQL!

А где лежат эти таблицы MySQL?

MySQL это типа программа, запущенная на компе. Это такой же сервер как и Counter-Strike. Все популярные хостинги предоставляют своим клиентам доступ к Базе Данных.

Как подключиться к MySQL?

Разберем какие данные нужны для подключения сервера кс1.6 к mysql

  • IP сервера MySQL — это IP адрес на котором находится MySQL. Он может быть буквенный(mysql.hosting.ru) или цифровой(12.34.56.78)
  • DataBase — это имя базы данных, в которой будут находиться таблицы со статистикой игроков, банами и прочим
  • User — имя пользователя, который имеет доступ к базе данных DataBase и вносить в ее таблицы изменения и и тп
  • Пароль — пароль, чтобы никто кроме вас и вашего сервера не имел доступ

Эти данные вводите например в конфиг csstats_mysql.cfg, при первом включении плагин создает таблицу под названием csstats_players в базе данных DataBase. И потом уже добавляет в нее игроков.

Связь сервера и сайта через MySQL

Тут не совсем связь, сервер пишет статистику в MySQL, а сайт берет информацию из MySQL и показывает ее пользователю в красивом оформлении.
Просто сайт своими средствами не может получить доступ, например к csstats.dat, но легко может получить доступ к БД

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

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

Тут можно создавать, удалять, очищать, обновлять и все все все.

Что такое SQL запросы?

Чтобы добавить/изменить/удалить или просмотреть запись в таблице, нужна сказать mysql чего мы хотим, человеческий язык он конечно не понимает, но понимает SQL запросы.
Чтобы добавить нового игрока в статистику игроков csstats_mysql, плагин посылает запрос:

Чтобы показать статистику игрока плагин шлет:

Это просто небольшие примеры SQL запросов, подробнее можете почитать в интернете.
Вы можете тоже слать SQL запросы в phpmyadmin, но вряд ли они Вам понадобятся, просто знайте 😉

Термины

• База Данных, БД, DataBase, DB — это все сама база данных, в ней хранится информация
• SQL — язык управления базами данных. То есть это и есть SQL запросы, которыми можно вносить изменения в базах
• СУБД — Система Управления Базами Данных. То есть это программа(сервер), которая управляет базами данных, принимает SQL запросы и тд. Есть много разных СУБД: SQLite, PostgreSQL, MySQL и другие. Самая популярная из них это MySQL
• MySQL — это СУБД, смотрите выше.
Старался писать простым, чтобы новички поняли суть. Более подробно можете почитать в интернете, информации на эту тему там много.

Ошибки MySQL, что они означают и как с ними бороться

Если в error_ логах Вы увидите ошибку, типа: Ошибка MySQL! или MySQL Error! и подобное, ЗНАЙТЕ! Это ошибка MySQL, а не плагина.
Если Вы не знаете английский, воспользуйтесь переводчиком, это не сложно.
Тут разберем несколько популярных ошибок

    Access denied for user ‘user’@’localhost’ (using password: YES)

Самая популярная ошибка, она говорит: Нет доступа для пользователя «user» в базе данных с адресом localhost(тут может писаться и айпи)
Может быть 3 причины:
1) Не верный логин или пароль. Проверьте внимательней, не вписали ли Вы случайно лишний символ, например пробел и пр.
2) Не включен удаленный доступ. В целях безопасности, нельзя подключатся к БД удаленно.
Т.е. если БД и кс сервер стоит на одном компе, то адресом Базы будет localhost или 127.0.0.1. В этом случае все будет работать из коробки.
Но если сервер КС стоит на одном хостинге например, а БД на другом, то нужно разрешить для пользователя «user» удаленное подключение к БД. В ispmanager это делается так:

3) Возможно Вы указали не верный адрес БД
4) Все! Другого не дано! Только эти 3 пункта.

MySQL — это что такое и где применяется?

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

MySQL имеет заслуженную репутацию. Это простая, эффективная и надежная система управления информацией, использующая традиционный язык SQL. В умелых руках MySQL — это реляционные идеи хранения и обработки информации в максимально широком спектре услуг, которые исторически сложились. Они отражают практическую целесообразность и представляют собой функциональность современной базы данных.

Рейтинг и место MySQL

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

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

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

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

В большинстве случаев объективная закономерность выводит разработчика именно на PHP/MySQL. Эта пара стала де-факто лучшей в программировании сайтов и иных ресурсов для локальных и распределенных сетей обработки информации.

Подключение базы данных

Нет ничего проще, чем присоединиться к базе данных.

Здесь на локальном хосте «localhost» находится база «sci_exchange_base» к которой происходит подключение пользователя «sci_iN_WMiX» с паролем «POi17DO». После успешного подключения база данных становится доступной для работы или создается «по новой» функцией scCreateTables().

Такая логика, «если ничего нет или есть проблема: создать все вновь в исходном виде» делает любой код любого ресурса безопасным. Нет ничего проще, чем создать вновь, а не разбирать завалы проблем, создаваемых многочисленными посетителями ресурса, ошибками линий связи и технических средств:

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

Использование базы данных

Практически любая операция с данными в PHP/MySQL — это запрос. Результат запроса — данные или изменение содержимого базы данных. Сама по себе MySQL — это инструментальное средство, которое использует свой функционал для исполнения своей функциональности.

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

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

В этом примере (1) — формирование строки запроса, (2) — исполнение запроса. Это обычное применение базы данных, описанное в MySQL manual, сложившееся на практике.

Здесь на вход функции передаются три параметра:

  • список полей таблицы;
  • имя таблицы;
  • условия выборки.

В результате MySQL query делается выборка данных, которые записываются в строку данных и возвращаются в качестве результата работы функции.

Независимое использование MySQL

Функция iLineSel() — метод объекта доступа к базе. Здесь не принципиально использование объектов для доступа к информации, но принципиально разделение функционала на тот, который зависим от самой базы данных, и тот, который зависим от кода ресурса.

В подавляющем большинстве случаев MySQL — это четыре основных процедуры:

Представив эти четыре действия четырьмя функциями, можно «абстрагироваться» от формирования запросов query в виде MySQL строки.

Функции iLineSel(), iLineIns(), iLineDel(), iLineUpd() используются в коде и требуют указания имен таблиц, имен полей, условий и значений. Это удобно для кода ресурса. Внутри функций используются строки query и конструкции для доступа к базе данных.

Совместимость и эффективность

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

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

Функциональная динамика

Опыт использования баз столь велик, что многие концептуальные вещи стали слишком привычными. Но пример того, как объективно и естественно ушла на задний план концепция совместимости, позволяет заметить: далеко не все таблицы базы данных «прямоугольные» и совершенно не все отношения в базе данных — «реляционные».

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

Последнее все чаще представляется нормальным и естественным. Широко применяемое объектно-ориентированное программирование в контексте баз данных, особенно в связке PHP/MySQL, позволяет создавать системы объектов, которые сами себя записывают в базу данных. Но поскольку эти объекты не обязательно всегда такие, какие были в момент создания, то и таблицы базы данных могут динамично меняться, когда хранят динамичные объекты.

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

Работа с MySQL в PHP

PHP поддерживает работу с базой данных MySQL.
Специальные встроенные функции для работы с MySQL позволяют просто и эффективно работать с этой СУБД: выполнять любые запросы, читать и записывать данные, обрабатывать ошибки.
Сценарий, который подключается к БД, выполняет запрос и показывает результат, будет состоять всего из нескольких строк. Для работы с MySQL не надо ничего дополнительно устанавливать и настраивать; всё необходимое уже доступно вместе со стандартной поставкой PHP.

Что такое mysqli?

mysqli (MySQL Improved) — это расширение PHP, которое добавляет в язык полную поддержку баз данных MySQL. Это расширение поддерживает множество возможностей современных версий MySQL.

Как выглядит работа с базой данных

Типичный процесс работы с СУБД в PHP-сценарии состоит из нескольких шагов:

  1. Установить подключение к серверу СУБД, передав необходимые параметры: адрес, логин, пароль.
  2. Убедиться, что подключение прошло успешно: сервер СУБД доступен, логин и пароль верные и так далее.
  3. Сформировать правильный SQL запрос (например, на чтение данных из таблицы).
  4. Убедиться, что запрос был выполнен успешно.
  5. Получить результат от СУБД в виде массива из записей.
  6. Использовать полученные записи в своём сценарии (например, показать их в виде таблицы).

Функция mysqli connect: соединение с MySQL

Перед началом работы с данными внутри MySQL, нужно открыть соединение с сервером СУБД.
В PHP это делается с помощью стандартной функции mysqli_connect() . Функция возвращает результат — ресурс соединения. Данный ресурс используется для всех следующих операций с MySQL.

Но чтобы выполнить соединение с сервером, необходимо знать как минимум три параметра:

Если вы следовали стандартной процедуре установки MySQL или используете OpenServer, то адресом сервера будет localhost , логином — root . При использовании OpenServer пароль для подключения — это пустая строка ‘’, а при самостоятельной установке MySQL пароль ты задавал в одном из шагов мастера установки.

Базовый синтаксис функции mysqli_connect() :

Проверка соединения

Первое, что нужно сделать после соединения с СУБД — это выполнить проверку, что оно было успешным.
Эта проверка нужна, чтобы исключить ошибку при подключении к БД. Неверные параметры подключения, неправильная настройка или высокая нагрузка заставит MySQL отвеграть новые подключения. Все эти ситуации приведут к невозможности соединения, поэтому программист должен проверить успешность подключения к серверу, прежде чем выполнять следующие действия.

Соединение с MySQL устанавливается один раз в сценарии, а затем используется при всех запросах к БД.
Результатом выполнения функции mysqli_connect() будет значение специального типа — ресурс.
Если подключение к MySQL не удалось, то функция mysqli_connect() вместо ресурса вернет логическое значение типа «ложь» — false .
Хорошей практикой будет всегда проверять значение результа выполнения этой функции и сравнивать его с ложью.

Соединение с MySQL и проверка на ошибки:


Функция mysqli_connect_error() просто возвращает текстовое описание последней ошибки MySQL.

Установка кодировки

Первым делом после установки соединения крайне желательно явно задать кодировку, которая будет использоваться при обмене данными с MySQL. Если этого не сделать, то вместо записей со значениями, написанными кириллицой, можно получить последовательность из знаков вопроса: ‘. ’.
Вызови эту функцию сразу после успешной установки соединения: mysqli_set_charset($con, «utf8»);

Выполнение запросов

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

Два вида запросов

Следует разделять все SQL-запросы на две группы:

  1. Чтение информации (SELECT).
  2. Модификация (UPDATE, INSERT, DELETE).

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

Добавление записи

Вернёмся к нашему проекту — дневнику наблюдений за погодой. Начнём практическую работу с заполнения таблиц данными. Для начала добавим хотя бы один город в таблицу cities.

Выражение INSERT INTO используется для добавления новых записей в таблицу базы данных.

Составим корректный SQL-запрос на вставку записи с именем города, а затем выполним его путём передачи этого запроса в функцию mysqli_query() , чтобы добавить новые данные в таблицу.

Обратите внимание, что первым параметром для функциии mysqli_query() передаётся ресурс подключения, полученный от функции mysqli_connect() , вторым параметром следует строка с SQL-запросом.
При запросах на изменение данных (не SELECT) результатом выполнения будет логическое значение — true или false.
false будет означать, что запрос выполнить не удалось. Для получения строки с описанием ошибки существует функция mysqli_error($link) .

Функция insert id: как получить идентификатор добавленной записи

Следующим шагом будет добавление погодной записи для нового города.
Погодные записи хранит таблица weather_log, но, чтобы сослаться на город, необходимо знать идентификатор записи из таблицы cities.
Здесь пригодится функция mysqli_insert_id() .
Она принимает единственный аргумент — ресурс соединения, а возвращает идентификатор последней добавленной записи.

Теперь у нас есть всё необходимое, чтобы добавить погодную запись.
Вот как будет выглядеть комплексный пример с подключением к MySQL и добавлением двух новых записей:

Чтение записей

Другая частая операция при работе с базами данных в PHP — это получение записей из таблиц (запросы типа SELECT).
Составим SQL-запрос, который будет использовать SELECT выражение. Затем выполним этот запрос с помощью функции mysqli_query() , чтобы получить данные из таблицы.

В этом примере показано, как вывести все существующие города из таблицы cities:

В примере выше результат выполнения функции mysqli_query() сохранён в переменной $result .
Важно понимать, что в этой переменной находятся не данные из таблицы, а специальный тип данных — так называемая ссылка на результаты запроса.

Чтобы получить действительные данные, то есть записи из таблицы, следует использовать другую функцию — mysqli_fetch_array() — и передать ей единственным параметром эту самую ссылку.
Теперь каждый вызов функции mysqli_fetch_array() будет возвращать следующую запись из всего результирующего набора записей в виде ассоциативного массива.

Цикл while здесь используется для «прохода» по всем записям из полученного набора записей.
Значение поля каждой записи можно узнать просто обратившись по ключу этого ассоциативного массива.

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

Иногда бывает удобно после запроса на чтение не вызывать в цикле mysqli_fetch_array для извлечения очередной записи по порядку, а получить их сразу все одним вызовом. PHP так тоже умеет. Функция mysqli_fetch_all($res, MYSQLI_ASSOC) вернёт двумерный массив со всеми записями из результата последнего запроса.
Перепишем пример с показом существующих городов с её использованием:

Как узнать количество записей

Часто бывает необходимо узнать, сколько всего записей вернёт выполненный SQL запрос.
Это может помочь при организации постраничной навигации, или просто в качестве информации.
Узнать число записей поможет функция mysqli_num_rows() , которой следует передать ссылку на результат запроса.

Часть 1. С чего начать

Серия контента:

Этот контент является частью # из серии # статей: Практическое использование MySQL++

Этот контент является частью серии: Практическое использование MySQL++

Следите за выходом новых статей этой серии.

Подавляющее большинство приложений в той или иной степени используют в процессе работы базы данных. При разработке таких приложений весьма полезными оказываются инструментальные средства, упрощающие взаимодействие с различными СУБД. Одним из таких инструментов является библиотека MySQL++, которая позволяет эффективно организовать доступ к базам данных MySQL из программ, написанных на языке C++. Я попытаюсь подробно разобрать функциональные возможности этой библиотеки, её достоинства и недостатки. Начну с общих характеристик MySQL++.

1. Что такое MySQL++

MySQL++ — это специализированная библиотека так называемых «обёрточных» (wrapper) методов для прикладного программного интерфейса C API для СУБД MySQL. Главная цель этой библиотеки — сделать работу с SQL-запросами такой же простой, как работа с STL-контейнерами.

Самую последнюю версию библиотеки MySQL++ можно найти на официальном Web-сайте. Тем не менее, чтобы избежать дополнительных сложностей, лучше обратиться к репозиторию пакетов для вашего дистрибутива.

1.1. Немного истории

В 1998 г. Кевин Аткинсон [Kevin Atkinson] начал разработку библиотеки, которая по его первоначальному замыслу обеспечивала бы выполнение SQL-запросов и обработку их результатов без привязки к какой-либо конкретной СУБД. Это было отражено даже в оригинальном названии — SQL++. Все версии, предшествующие 1.0, являются плодом индивидуальной работы Кевина.

В 1999 г. библиотекой занялась компания MySQL AB. Сначала некоторую работу проделал Майкл «Монти» Видениус [Michael «Monty» Widenius], затем он передал полномочия другому сотруднику MySQL AB Синише Миливоевичу [Sinisa Milivojevic]. Были выпущены версии 1.0 и 1.1, после чего Аткинсон официально передал все функции сопровождения библиотеки в руки Миливоевича и полностью устранился от какого бы то ни было участия в данном проекте. Миливоевич довёл библиотеку до версии 1.7.9, которая была выпущена в середине 2001 г. К этому моменту стала очевидной невозможность реализации универсальной библиотеки SQL-запросов, независимой от конкретных реализаций СУБД. Ориентация на MySQL стала неизбежной.

После выпуска версии 1.7.9 в работе над MySQL наступил период некоторого застоя, который продолжался три года, до августа 2004 г., когда ситуацию под контроль взял Уоррен Янг [Warren Young]. Уоррен сразу же выпустил версию 1.7.10, устранил все проблемы с компиляцией при использовании GCC, исправил большое количество ошибок, добавил новые возможности. В общем, как говорится, «процесс пошёл».

В данной статье я рассматриваю версию 3.0.9 библиотеки MySQL++. На официальном Web-сайте эта версия объявлена как «последняя стабильная».

2. Краткое описание основных объектов (соединение, запрос, результаты)

MySQL++ обеспечивает поддержку большинства разнообразных способов и приёмов работы с базами данных. И всё же, несмотря на это разнообразие, можно выделить обобщённую схему использования API-интерфейса, предназначенного для доступа к базам данных:

  • создание (открытие) соединения с базой данных,
  • формирование и выполнение запроса,
  • при успешном выполнении запроса — обработка итогового набора (result set) — итеративный последовательный проход по записям этого набора,
  • если выполнение запроса завершилось неудачно, то необходимо обеспечить обработку ошибок (исключений).

Каждому из перечисленных выше этапов соответствует класс или иерархия классов библиотеки MySQL++. Рассмотрим их немного подробнее.

2.1. Объект Connection (соединение)

Объект Connection управляет соединением с сервером MySQL. Для того чтобы выполнять какие-либо операции в базе данных, необходим по меньшей мере один такой объект. В приложении использование всех прочих MySQL++-объектов зависит (хотя и не всегда непосредственно) от экземпляра Connection, поэтому пока в вашей программе применяются объекты библиотеки MySQL++, должен существовать и объект Connection.

В СУБД MySQL допускается использование нескольких различных типов соединений между клиентом и сервером: сокеты TCP/IP, сокеты Unix-домена, именованные программные каналы. Базовый класс Connection поддерживает все эти типы соединений. Какой именно тип соединения необходим в каждом конкретном случае, вы определяете с помощью параметров, передаваемых в метод Connection::connect(). Но если вы заранее решили, что ваше приложение будет работать только с одним типом соединений, то вашему вниманию предлагаются специализированные подклассы с упрощёнными интерфейсами. Например, если в программе предполагаются обращения исключительно к сетевому серверу баз данных, то вы можете воспользоваться подклассом TCPConnection.

2.2. Объект Query (запрос)

Чаще всего SQL-запросы создаются с использованием объекта Query, инициализируемого объектом Connection.

Query работает практически аналогично потоку вывода в стандартном C++, поэтому вы можете записывать в него данные так же, как в std::cout или std::ostringstream. Это наиболее близкий стилю языка C++ способ, используемый MySQL++ для компоновки строк запросов. В библиотеку включены манипуляторы потока с контролем типов данных, что существенно упрощает создание синтаксически корректных SQL-команд.

Ещё одной функциональной особенностью Query являются запросы-шаблоны (Template Queries), которые в определённой степени напоминают функцию языка C printf: формируется строка запроса с включаемыми в неё тэгами, обозначающими места вставки переменных элементов данных. Это удобно в тех случаях, когда в программе используются многочисленные запросы с одинаковой структурой — определив один шаблон, вы можете применять его многократно в различных частях своего приложения.

Третий метод создания запросов — использование объекта Query совместно со специализированными структурами SSQLS (Specialized SQL Structures), которые позволяют создавать структуры C++, являющиеся точным отображением схем конкретных баз данных. Это даёт объекту Query информацию, необходимую для формирования обобщённых SQL-запросов.

2.3. Наборы результатов (Result Sets)

Данные полей в наборе результатов запроса сохраняются в специальном классе с именем String (аналог стандартного std::string). Этот класс предоставляет операторы для автоматизированного преобразования объектов наборов результатов в любой базовый тип C/C++. Кроме того, MySQL++ определяет классы, такие как DateTime, которые вы можете инициализировать данными SQL-типа DATETIME. Для этих автоматических преобразований осуществляется контроль их корректности, и при ошибках конвертации выставляется соответствующий флаг-предупреждение или генерируется исключение (в зависимости от настроек библиотеки).

Для предоставления результатов выполнения SQL-команд в библиотеке MySQL++ реализованы следующие подходы.

2.3.1. Команды, не возвращающие данные

Не все команды SQL возвращают данные. Пример такой команды CREATE TABLE. Для подобных команд имеется специальный тип результата (SimpleResult), который возвращает только состояние после выполнения команды: успешное завершение выполнения команды, количество строк, на которые подействовала команда (если подразумевалось какое-либо воздействие), и т.п.

2.3.2. Запросы, возвращающие данные: структуры данных MySQL++

Самый простой способ получения набора результатов — использование метода Query::store(). Этот метод возвращает объект StoreQueryResult, являющийся производным от std::vector , представляющий собой контейнер с произвольным доступом, сформированный из Row-строк. В свою очередь, каждый объект Row является аналогом вектора (std::vector) строк (тип String), по одному объекту на каждое поле в наборе результатов. Таким образом, вы можете рассматривать StoreQueryResult как двумерный массив, т.е. чтобы получить пятое поле из второй строки, можно просто написать result[1][4]. К полям можно обращаться и по их именам, поэтому возможна и такая форма записи: result[1][«price»].

Несколько менее удобным способом работы с набором результатов является применение метода Query::use(), возвращающего объект UseQueryResult. Этот класс функционирует подобно стандартному STL-итератору. В этом случае произвольного доступа к данным уже не получится — вы последовательно проходите по строкам набора результатов с ограничением по направлению: только от начала к концу. Нет возможности возвращаться к уже пройденным строкам, и вы не знаете, сколько строк в данном наборе до тех пор, пока не достигнете последней строки. В качестве компенсации за эти неудобства вы получаете более рациональное использование оперативной памяти, поскольку нет необходимости загружать в память весь набор полностью. Это особенно важно, если приходится работать с чрезвычайно большими наборами результатов.

2.3.3. Запросы, возвращающие данные: специализированные структуры SSQLS

Доступ к результатам запросов посредством структур данных библиотеки MySQL++ представляет достаточно низкий уровень абстракции — это лучше, чем применение MySQL C API-интерфейса, но не намного. Приблизить логику решения к предметной области задачи можно с помощью специализированных структур SSQLS (Specialized SQL Structures). Эти SSQLS-объекты позволяют определять структуры языка C++, которые соответствуют структурам таблиц в схеме конкретной базы данных. Кроме того, гораздо проще состыковать SSQLS-объекты со стандартными STL-контейнерами (а следовательно, и с алгоритмами).

Преимущество этого способа заключается в том, что в программе потребуется включение минимального объёма SQL-кода. Можно выполнить запрос и получить результат в виде структур данных языка С++, доступ к которым не отличается от доступа к любым другим структурам. Доступ к полученным данным можно организовать через объект Row или же обратиться к методам библиотеки, чтобы «сбросить» результаты в STL-контейнер — с последовательным, произвольным или ассоциативным доступом — выбор за вами.

Рассмотрим следующий фрагмент кода:

Практически «чистый» код на C++, без излишеств.

Если по каким-либо причинам создание SSQLS-объектов, соответствующих структурам таблиц баз данных, нежелательно или невозможно, то вы можете использовать объект Row, и приведённый выше фрагмент кода теперь будет выглядеть так:

Различия невелики. Первый фрагмент выглядит более лаконично, но на внутреннюю логику это не влияет.

2.4. Ошибки и исключения

По умолчанию при возникновении ошибок библиотека генерирует исключения (exceptions). При необходимости вы можете настроить вместо генерации исключений установку специального флага ошибки (error flag), но помните, что исключения предоставляют более подробную информацию. В пределах одного блока try-catch вы можете выявлять различные типы ошибок.

3. Простой пример использования MySQL++

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

Для обеспечения работы примера необходимо создать базу данных MySQL с именем test_db, содержащую таблицу с именем dvd. Эта таблица может иметь, например, следующую структуру:

После того как тестовая база данных test_db создана, администратор MySQL должен создать пользователя с правами работы в ней. Имя и пароль для этого пользователя уже были указаны в приведённом выше исходном коде примера.

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

Что касается самой программы, то она запрашивает только поле имён (name) из таблицы dvd, и из полученного набора результатов res последовательно выводит все строки (названия DVD). Пример очень простой, но в нём проиллюстрированы три из четырёх этапов, упомянутых в начале данной статьи. О четвёртом этапе — обработке исключений — речь пойдёт в одной из следующих статей цикла.

4. Заключение

Даже после первого, достаточно беглого взгляда на функциональные свойства и характеристики библиотеки MySQL++ становится понятно, что она способна оказать существенную помощь разработчикам приложений, взаимодействующих с серверами СУБД MySQL. Библиотека легко адаптируется к потребностям программиста, не содержит ненужных излишеств и без затруднений «вписывается» в исходный код на языке C++.

В данной статье рассматривались общие характеристики библиотеки MySQL++, её функциональные возможности. В качестве иллюстрации был приведён простой пример исходного кода с применением объектов MySQL++. Следующая статья цикла будет посвящена настройке и адаптации библиотеки MySQL++ в различных проектах. В третьей статье будет подробно описано выполнение SQL-запросов и обработка их результатов. Тема четвёртой статьи — обработка ошибок и использование транзакций. В пятой статье мы рассмотрим различные типы данных и работу с ними. Специализированным формам запросов уделяется особое внимание в шестой статье . В заключительной, седьмой статье цикла будет продемонстрировано практическое применение библиотеки MySQL++ в многопоточных приложениях.

Оптимизация MySQL (просто о сложном)

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

Делайте запросы MySQL удобными для кэширования

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

Дело в том, что в первом запросе была использована функция CURDATE(), особенность её работы не позволяет помещать результаты запроса в кэш. Значение даты можно предварительно записать в строку запроса, это позволит исключить использование функции CURDATE() в запросе.
По аналогии есть и другие функции, которые не кэшируются самим сервером MySQL, среди них RAND(), NOW() а так же другие функции, результат которых недетерминирован.

Просмотрите как выполняется ваш запрос с помощью синтаксиса EXPLAIN

Посмотреть, как MySQL выполняет ваш запрос можно с помощью синтаксиса EXPLAIN. Его использование может помочь определить слабые места в производительности запроса, а так же в структуре таблиц. В качестве результата запроса EXPLAIN возвратит данные, которые покажут, какие используются индексы, каким образом выбираются данные из таблиц, как сортируются, и т.д. Для этого достаточно добавить вначале SELECT-запроса ключевое слово EXPLAIN, после чего будет показана таблица, с данными.

Когда вам нужна одна запись, выставляйте LIMIT 1

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

Индексируйте поля по которым производится поиск

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

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

Индексируйте поля по которым объединяются таблицы

Если вы используйте множество объединений таблиц, то вам стоит задуматься о том, чтобы поля, участвующих в объединении были проиндексированы в обеих таблицах. Это дело влияет на то, как MySQL будет производить внутреннюю оптимизацию объединений полей таблицы. Поля объединения должны быть одного типа и одной кодировки. Т.е. к примеру, если одно поле будет иметь тип DECIMAL, а другое INT, то MySQL не сможет воспользоваться индексом.


Найдите альтернативу вместо ORDER BY RAND()

Использование рандомной сортировки действительно является весьма удобным, и об этом такого же мнения многие начинающие программисты. Однако тут есть подводные камни, и очень весомые, используя подобный метод выборки в своих запросах, вы оставляете узкое место в производительности. Здесь же рекомендуется прибегнуть к дополнительному коду вместо использования ORDER BY RAND(), в качестве альтернативы, чтобы избавиться от слабого места в производительности, которое напомнит о себе при увеличении объема данных.

Используйте выборку конкретных полей, вместо SELECT *

Не ленитесь указывать конкретные нужные поля в запросе при выборке, вместо использования «*» — выборка всех полей, дело в том, что чем больше данных считывается из таблицы, тем медленнее становиться ваш запрос.

Добавляйте поле ID для всех таблиц

Каждая таблица в хорошем её исполнении должна иметь поле id типа INT, которое является первичным ключом (PRIMARY_KEY), и AUTO_INCREMENT. Кроме того, для поля нужно указать параметр UNSIGNED, который означает то, что значение всегда будет положительным.
В MySQL есть внутренние операции, которые могут использовать первичный ключ, это играет роль для сложных конфигураций баз данных, таких как кластеры, распараллеливание, и т.д.
Кроме того, если есть несколько таблиц, и необходимо выполнить объединенный запрос, то тут ID таблиц окажется весьма кстати.

ENUM как альтернатива VARCHAR

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

Используйте значение NOT NULL вместо NULL

Поля NULL занимают больше места в записи, из-за того что возникает необходимость отмечать это NULL значение. Таблицы MyISAM, поля с NULL хранятся таким образом, что каждое поле занимает 1 дополнительный бит, который округляется до ближайшего байта. Если использование NULL в поле не принципиально, то рекомендуется использовать NOT NULL.

Пользуйтесь Prepared Statements

Prepared Statements (подготовленные выражения, их так же называют связываемыми переменными) — это часть функциональности SQL-баз данных, предназначенная для отделения данных запроса и собственно выполняемого SQL-запроса. Его использование имеет преимущества в плане безопасности, и производительности. Фильтруя значения данных, добавляемых в запрос, prepared statements таким образом защищает базу данных от SQL инъекций. Разумеется, делать подобные проверки можно и в ручную, однако в этом случае есть вероятность допустить ошибку из-за невнимательности. Последние версии MySQL компилируют prepared statements в бинарную форму, это позволяет повысить эффективность его работы. Выполняя множество однотипных запросов в приложении, MySQL будет разбирать запрос только один раз. На первых этапах prepared statements не имело возможности кэшироваться в MySQL, это являлось веской причиной для его игнорирования и отсутсвия желания использовать в своих проектах. Однако начиная с версии MySQL 5.1 ситуация кардинально поменялась.

Пользуйтесь mysql_unbuffered_query

mysql_unbuffered_query() посылает запрос MySQL query без автоматической обработки и буферизации её результата, в отличие от функции mysql_query(). Это позволяет сохранить достаточно большое количество памяти для SQL-запросов, возвращающих большое количество данных. Кроме того, вы можете начать работу с полученными данными сразу после того, как первый ряд был получен: вам не приходится ждать до конца SQL-запроса.

Вы можете хранить IP-адреса в поле с типом INT (UNSIGNED)

Для хранения IP-адресов в привычном виде многие хранят в таблице с полем типа VARCHAR(15), и лишь не многие используют целочисленный тип для этого. Плюсы в том, тип INT занимает 4 байта и имеет фиксированный размер поля. Поле типа INT должно быть UNSIGNED, т.е. целочисленным, в запросе следует использовать функцию INET_ATON(), которая будет конвертировать IP-адрес в число. Обратное преобразование выполняется с помощью функции INET_NTOA().

Используйте статичные таблицы

Статичная таблица это обычная таблица в базе, за исключеним того, что каждое поле в таблице имеет фиксированный размер. Если в таблице есть колонки, не фиксированной длины, к примеру, это могут быть: VARCHAR, TEXT, BLOB, она перестает быть статичной, и будет обрабатываться MySQL немного иначе. Статичные таблицы, или их можно ещё назвать таблицами фиксированного размера работают быстрее не статичных. Записи из таких таблицах будут просматриваться быстрее, при необходимости выбора нужной строки MySQL быстро вычислит её позицию. Если поле имеет не фиксированный размер, то в этом случае поиск производиться по индексу. Есть и другие плюсы использования статических таблиц, дело в том, что эти таблицы проще кэшируются, а так же восстанавливаются после падения базы данных.

Используйте вертикальное разделение

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

Разделяйте объемные запросы INSERT и DELETE

Выполнение большого объема запросов такого рода может привести к блокировке таблицы, вследствие чего, к неправильной работы приложения в целом. Параллельные запросы на веб-сервер могут порождать дополнительное обращение к таблице. Если таблица заблокирована предыдущим запросом, последующие запросы выстраиваются в очередь, и как следствие это проявляется в виде торможения сайта, а то и падения сервера.
Если вам необходимо сделать множество запросов, постарайтесь контролировать их, отдавая небольшими сериями, а не скидывать всё на базу данных. При этом возможно ваш запрос будет выполняться дольше, но это менее скажется на других пользователях.
Пример:

Стремитесь использовать поля небольшого размера

Как известно данные базы хранятся на жестком диске, это зачастую это может оказаться одним из слабых мест в веб-приложении. Дело в том, что записи небольшого размера являются более предпочтительными, т.к. использование их уменьшает работу с жестким диском. Если вы уверенны, что конкретная таблица будет хранить мало строк, то рациональным решением будет использование типов полей, с минимальными возможными значениями. К примеру, если основной ключ имеет тип INT, и вы будете хранить в таблице лишь небольшое кол-во данных, то лучше сделать его типа MEDIUMINT, SMALLINT или даже TINYINT.

Выбирайте тип таблиц под свои задачи

Два широко известных типа таблиц на сегодняшний день, это MyISAM и InnoDB, каждый из них имеет свои положительные и отрицательные стороны. К примеру, MyISAM хорошо считывает данные из таблиц в большом объеме, одно он более медлителен при записи. Он так же хорошо выполняет запросы вида SELECT COUNT(*).
Механизм хранения данных у InnoDB более сложный, чем у MyISAM, однако, он поддерживает блокировку строк, что является положительной стороной при масштабировании. Поэтому сказать, что одно лучше другого нельзя, да и не правильно, нужно выбирать тип исходя из своих потребностей.

Простая работа с MySQL в простых примерах #1 — работа с базами, создание таблиц

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

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

Будет приведен пример работы с MySQL как из консоли сервера, так и и из mysql -клиента — просмотр баз, таблиц в них. Будет приведен пример создания простой таблицы, описание типов используемых типов столбцов в ней. Во второй части будет дано описание и примеры работы с таблицей — наполнение данными, редактирование их и прочее.

Для примера будет создана таблица, содержащая список контактов.

Уточнение: MySQL — это сервер баз данных, а mysql — это консольный unix-like клиент, для работы с MySQL. Строки, начинающиеся с символа $ означают, что они выполняются из обычной консоли сервера.

Для подключения к серверу MySQL используется такая команда:

$ mysql -u username -p
Enter password:
mysql>

Если сервер MySQL находится на удалённом хосте — его можно указать с помощью ключа -h :

$ mysql -u username -p -h somehost.com
Enter password:
mysql>

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

mysql> status
—————
mysql Ver 14.14 Distrib 5.5.29, for FreeBSD9.0 (i386) using 5.2

Connection id: 107801
Current database: test1
Current user: username@localhost
SSL: Not in use
Current pager: most
Using outfile: »
Using delimiter: ;
Server version: 5.5.29-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 8 days 1 hour 31 min 32 sec

Threads: 5 Questions: 8391067 Slow queries: 37 Opens: 17162 Flush tables: 1 Open tables: 64 Queries per second avg: 12.044

Ещё полезная возможность — выполнять консольные команды сервера, не выходя из клиента mysql , для этого используйте команду system :

mysql> system w;
8:12PM up 8 days, 1:40, 4 users, load averages: 0.20, 0.29, 0.26
USER TTY FROM LOGIN@ IDLE WHAT
setevoy pts/1 attached-masquerader.vol 10:43AM 9:28 htop
setevoy pts/2 attached-masquerader.vol 10:43AM — w
setevoy pts/3 attached-masquerader.vol 10:43AM 54 mysql -u root -p
setevoy pts/4 attached-masquerader.vol 10:43AM 53 /usr/local/bin/bash

Теперь — примеры использования некоторых SQL-функций.

К примеру — выведем текущую дату:

mysql> select now();
+———————+
| now() |
+———————+
| 2013-01-27 15:56:20 |
+———————+
1 row in set (0.00 sec)

Можно выполнить запрос нескольких функций, для этого — после первого запроса не ставим закрывающие точку с запятой:

Если в процессе вы передумали продолжать набор команд и хотите завершить набор — укажите с :

mysql> select now(),
-> c
mysql>

Что бы отобразить список доступных баз — используйте команду:

Что бы переключиться на использование определённой базы — используйте use :

mysql> use base1;
Database changed

Что бы проверить, какая в данный момент база используется — выполните:

mysql> select database();
+————+
| database() |
+————+
| base1 |
+————+
1 row in set (0.00 sec)

Просмотреть содержащиеся в ней таблицы можно командой:

mysql> show tables;
+———————+
| Tables_in_base1 |
+———————+
| tablename |
+———————+
1 row in set (0.00 sec)

В базе base1 имеется только одна таблица tablename .

Всё то же самое можно сделать и не подключаясь к серверу MySQL, а напрямую из консоли сервера. Например — просмотреть список имеющихся на сервере баз:

$ mysqlshow -u root -p
Enter password:
+———————+
| Databases |
+———————+
| base1 |
| base2 |
| base3 |
| base4 |
+———————+

А просмотреть имеющиеся в базе данных MySQL таблицы из консоли можно так:

$ mysqlshow base1 -u root -p
Enter password:
Database: base1
+————+
| Tables |
+————+
| tablename |
+————+

Посмотреть содержимое таблицы из консоли можно следующим образом:

$ mysqlshow base1 tablename -u root -p

Учтите, что если название таблицы содержит символ подчеркивания «_» — то mysqlshow может неверно обработать запрос, и вывести примерно следующее:

$ mysqlshow base1 tablename_1 -u root -p
Enter password:
Database: contacts Wildcard: tablename_1
+————-+
| Tables |
+————-+
| tablename_1 |
+————-+

Что бы избежать этого — добавьте символ % в конец запроса:

$ mysqlshow base1 tablename_1 % -u root -p
Enter password:
Database: contacts Table: tablename_1
+——————+——————+——+——+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——————+——————+——+——+———+—————-+
| contact_id | int(10) unsigned | NO | PRI | NULL | auto_increment |

Выполнять запросы SQL можно как из консоли сервера, так и из консоли клиента mysql . Так же, запросы можно вводить не напрямую, а из файла. К примеру, имеется файл с таким содержимым:

$ cat temp.sql
select now()

Что бы выполнить этот запрос из консоли сервера — выполните:

А из клиента mysql — так:

mysql> . temp.sql
+———————+
| now() |
+———————+
| 2013-01-31 18:00:48 |
+———————+
1 row in set (0.00 sec)

При этом, файл должен находится в каталоге, в котором вы находились перед подключением к mysql -клиенту.

Теперь — подключимся под пользователем root и создадим базу test1 :

# mysql -u root -p
Enter password:
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

Далее — создадим пользователя user1 , дадим ему права на доступ к базе test1 и установим пароль mypasswd :

mysql> grant all on test1.* to ‘user1’@’localhost’ identified by ‘mypasswd’;

Теперь — подключимся к серверу под новым пользователем и сразу переключимся на базу test1 , что бы избежать ввода команды use database , после чего сразу проверим используемую базу с помощью функции select database() :

# mysql -u user1 -p test1
Enter password:
mysql> select database();
+————+
| database() |
+————+
| test1 |
+————+
1 row in set (0.00 sec)

При создании таблицы используется такой синтаксис:

имя_столбца ТИП(значение) АТРИБУТ

Атрибуты могут иметь значение либо NULL (может быть пустым), либо NOT NULL — обязательно должен быть заполнен.

В примере будут использоваться такие типы столбцов:

VARCHAR — подразумевает, что в столбце будут использоваться символьные данные — текст — переменной длины, в (значение) указывается количество знаков, которое может быть использовано в этом столбце;
DATE — подразумевает использование даты в формате YYYY-MM-DD ;
INT — только целые числа, без дробей;
UNSIGNED — только положительные значения;
AUTO_INCREMENT — автоматически задаст значение, на единицу большее существующего;
PRIMARY KEY — индексное значение, всегда должно быть уникальным;
ENUM — столбец перечисляемого типа, может принимать значения, указанные в его атрибутах, в примере это (‘Y’, ‘N’) .

PRIMARY KEY указывает, что указанные столбцы являются индексами, подробнее тут>>> .

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

mysql> CREATE TABLE main_list (contact_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50), phone VARCHAR(20), city VARCHAR(20), email VARCHAR(50), skype VARCHAR(20), icq INT UNSIGNED, birth_date DATE, facebook_address VARCHAR(50), vk_address VARCHAR(50), real_type ENUM(‘Y’, ‘N’));
Query OK, 0 rows affected (0.26 sec)

Что бы просмотреть созданную таблицу — выполним:

mysql> desc main_list;
+——————+——————+——+——+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——————+——————+——+——+———+—————-+
| contact_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(50) | NO | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| skype | varchar(20) | YES | | NULL | |
| icq | int(10) unsigned | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| facebook_address | varchar(50) | YES | | NULL | |
| vk_address | varchar(50) | YES | | NULL | |
| real_type | enum(‘Y’,’N’) | YES | | NULL | |
+——————+——————+——+——+———+—————-+

Добавим вторую таблицу:

mysql> CREATE TABLE main_socials (entry_id INT UNSIGNED AUTO_INCREMENT NOT NULL, contact_id INT UNSIGNED NOT NULL, vk_id VARCHAR(100), facebook_id VARCHAR(100), PRIMARY KEY (entry_id, contact_id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc main_socials;
+————-+——————+——+——+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————-+——————+——+——+———+—————-+
| entry_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| contact_id | int(10) unsigned | NO | PRI | NULL | |
| vk_id | varchar(100) | YES | | NULL | |
| facebook_id | varchar(100) | YES | | NULL | |
+————-+——————+——+——+———+—————-+
4 rows in set (0.00 sec)

При указании столбцу параметра AUTO_INCREMENT необходимо так же установить определение индекса — PRIMARY KEY во избежание ошибки такого плана:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Так же, если вы хотите в таблице использовать более индекса 1 PRIMARY KEY — его необходимо указать в конце запроса, в виде отдельного значения:

… , PRIMARY KEY (entry_id, contact_id));

В противном случае — вы можете получить ошибку такого плана:

ERROR 1068 (42000): Multiple primary key defined

Если таблица создана ошибочно, или больше не нужна — удалить её можно командой:

mysql> drop table tablename;
Query OK, 0 rows affected (0.75 sec)

Если в таблице имеются лишние столбцы — удалить их можно так:

mysql> ALTER TABLE tablename DROP COLUMN column_name;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

Так же, полезная страница команд MySQL тут>>> и кратко о работе с резервными копиями — тут>>> .

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

Добавить комментарий