Ругулярные выражения в MySQL. REGEXP in MySQL

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

Примеры использования REGEXP:
SELECT * FROM `table` WHERE `name` REGEXP 'Ser.+'
Пример с использованием LIKE:

SELECT * FROM `table` WHERE `name` LIKE 'Ser%'

В обоих случаях будут выбраны записи, у которых в поле `name` находяться значения которые начинаются на "Ser"

Теперь приведу более сложный пример:

SELECT * FROM `table` WHERE `name` REGEXP  '^(A|B|C)'

Выбираем все имена, которые начинаються на A,B или C

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

Правила построения запросов являются стандартными, а это значит что тренироваться можно на сайте Rubular. Где также, всегда можно посмотреть и на сами правила построения.

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

Stored procedures - что это?

Хранимые процедуры появились начиная с 5 версии MySQL. Они позволяют автоматизировать сложные процессы на уровне MySQL, нежели использовать для этого внешние скрипты. Это даёт нам наиболее высокую скорость выполнения, т.к. мы не гоняем большое количество запросов, а всего лишь один раз вызываем ту или иную процедуру (или функцию).

Что для этого нужно? Установите MySQL сервер версии 5 или выше (dev.mysql.com/downloads). Процедуры можно создавать как запросы, например через командную строку MySQL, но для удобства советую скачать MySQL GUI Tools (dev.mysql.com/downloads/gui-tools). Данный пакет включает в себя три программы - MySQL Administrator, MySQL Query Browser и MySQL Migration Toolkit. Нам понадобятся первые две. (Хотя можно обойтись одним MySQL Query Browser, но все эти $$ в хранимых процедурах иногда могут сбить с толку).

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

You can store a value in a user-defined variable in one statement
and then refer to it later in another statement. This enables you
to pass values from one statement to another.
User-defined variables are
connection-specific. That is, a user variable defined
by one client cannot be seen or used by other clients. All
variables for a given client connection are automatically freed
when that client exits.

User variables are written as
@var_name, where the
variable name var_name consists of
alphanumeric characters, “.”,
“_”, and
“$”. A user variable name can
contain other characters if you quote it as a string or identifier
(for example, @'my-var',
@"my-var", or @`my-var`).

ORM в мире PHP

Многие проекты новые и ещё альфа/бета версии, но в любом случае уже есть возможность из чего выбрать.
• Propel
• Doctrine
• dOrm
• Outlet PHP ORM
• RedBean
• phpORM
• phpDataMapper
• Qcodo
• CoughPHP
• PHPLinq
• LightOrm for PHP, LightOrm on Freshmeat
• PHP ActiveRecord, PHP ActiveRecord on GitHub
• phpersistence
• Rocks PHP Library
• Pork.dbObject
• Repose
• ORMer
• Porte
• DaBase, DaBase — не очередная ORM для PHP


ORM в разных PHP Framework:
• Zend Framework Zend_Db_Mapper
• Zend Framework Db Table ORM
• Kohana ORM
• CIEXtended: based on CodeIgniter
• Xyster
• Solar’s Solar_Sql_Model


Active Record (AR) в разных PHP Framework:

• Yii Active Record
• CakePHP
 

Что можно почитать:

• ORM и с чем его едят
• Шаблон DataMapper
• List of object-relational mapping software: PHP
• Comprehensive list of the object relational mapping tools and frameworks. part one: php
• ORM in the PHP World
• ORM in PHP
• PHP ActiveRecord with PHP 5.3
• LightOrm: Описание на русском языке
• PHP ActiveRecord Available for Beta Testing
• Symfony Converting Propel Project To Doctrine

MySQL, удаление дубликатов

Существует множество универсальных решений данной задачи, но в MySQL всё решается ещё проще:

ALTER IGNORE TABLE my_table ADD UNIQUE INDEX(a, b);

SQL_CALC_FOUND_ROWS и с постраничный вывод информации

Любой web-программист сталкивался с постраничным выводом информации. Например, в таблице лежит 1000 новостей и нужно выводить их по 10 штук на странице, а внизу вывести листалку страниц.

Программист пишет 2 запроса:

1) SELECT * FROM news WHERE activity=1 LIMIT 0, 10 // выборка записей
2) SELECT COUNT(*) FROM news WHERE activity=1// узнаем кол-во всех новостей

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

В запросе SELECT перед списком столбцов необходимо указать опцию SQL_CALC_FOUND_ROWS. Вот начало описания синтаксиса конструкции SELECT.

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, …

Таким образом, выполняя запрос SELECT SQL_CALC_FOUND_ROWS MySQL подсчитает полное число строк, подходящих под условие запроса без LIMIT, и сохранить это число в памяти. Естественно, имеет смысл запрос SELECT SQL_CALC_FOUND_ROWS только при использовании ограничения (LIMIT). Сразу после выполнения запроса на выборку для получения количества записей нужно выполнить еще один SELECT-запрос: SELECT FOUND_ROWS ();. В результате MySQL вернет одну строку с одним полем, в котором и будет храниться число строк.
Пример запросов:

1) SELECT SQL_CALC_FOUND_ROWS * FROM news WHERE activity=1 LIMIT 10
2) SELECT FOUND_ROWS();

Первый запрос вернет (выведет) 10 строк таблицы news, для которых выполняется условие activity=1. Второй вызов команды SELECT возвратит количество строк, которые возвратила бы первая команда SELECT, если бы она была написана без выражения LIMIT. Хотя при использовании команды SELECT SQL_CALC_FOUND_ROWS, MySQL должен пересчитать все строки в наборе результатов, этот способ все равно быстрее, чем без LIMIT, так как не требуется посылать результат клиенту.
Пример запросов из PHP:

$result = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM news LIMIT  10");
while ($row = mysql_fetch_assoc($result)) {
    var_dump($row);
}
$num_rows = mysql_num_rows($result);
echo "$num_rows Rows";

В результате выполнения кода PHP выведет 10 строк из таблицы news , а затем целочисленное значение количества строк, соответствующих запросу (без учета LIMIT).

Phpstorm настройка подключения к Mysql

Follow this steps to active a MySQL connection (I used this for my local MAMP setup)

1) Install DB navigator plugin: Settings >> plugins >> "available" tab
2) Download MySQL jdbc connector >> http://www.mysql.com/downloads/connector/j/
3) Unpack MySQL jdbc connector to a directory (ex: /home/documents/mysql_connector)
4) Add a DB connection: Settings >> DB Connection

Example for MySQL
Name: personal connection name (ex: MAMP MySQL)
Description: some more information (ex: local mamp mysql server)
Driver Library: location of the MySQL jdbc connector (/home/documents/mysql_connector/mysql-connector-xxxx-bin.jar)
Driver: the perfered driver
URL: the connection URL with jdbc reference (ex: "jdbc:mysql://localhost:8889/")

user: the password for the mysql connection (ex MAMP: root)
password: the password for the mysql connection (ex MAMP: root)

Утилита mysqldump

Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.
Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.

Создание дампа

Разберем пример простейшее использования, задампим базу данных «database» при помощи перенаправления потока в файл «database.sql»:

mysqldump -uroot -h82.82.82.82 -p database > database.sql

где:

• -u или -–user=... — имя пользователя
• -h или --host=... — удаленный хост (для локального хоста можно опустить этот параметр)
• -p или --password — запросить пароль
• database — имя базы данных
• database.sql — файл для дампа

Для того чтобы сделать дамп несколько баз данных, необходимо использовать параметр --databases (или сокращенно -B), пример:

mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql

А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр --all-databases (или сокращенно -A), пример:

mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql

Развертывание дампа

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

mysql -uroot -h82.82.82.82 -p database < database.sql

Или через mysql-console:

mysql> use database;


mysql> source database.sql

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

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

mysqldump -uroot -h82.82.82.82 -p --where="true limit 100" database > database.sql

Или нам нужна только структура, без данных:

mysqldump -uroot -h82.82.82.82 -p --no-data database > database.sql

Примеры навеяны постом Александра Макарова — http://rmcreative.ru/blog/post/ljogkiy-damp-mysql

Вставка или обновление записи в MySQL

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

    Объясню попроще. Пусть у нас есть две таблицы: users – таблица с данными пользователей с полями id, login, password, где id – первичный ключ; есть таблица users_rating с рейтингом пользователей, поле – user_id – внешний ключ id в users, поле rating – сам рейтинг.

  Наша задача – обновить рейтинг пользователя, если он уже имеет какой-либо рейтинг, либо вставить запись о пользователе и его начальном рейтинге в таблицу.

Используем SELECT для вставки строк

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

INSERT INTO businesses_categories (id_business, id_category)

SELECT id, id_category FROM business ORDER BY id

Подсчет уникальных значений в столбце

SELECT 
nbsp;   count( DISTINCT table_field )FROM 
    TABLE

Удаление одним запросом записи в нескольких таблицах (MySQL)

Для того чтобы вывести эту информацию единой в едином виде придется произвести объединение выдачи:

SELECT
tablesql.active, tablesql.id, tablesql_text.text_table

FROM
tablesql
INNER JOIN tablesql_text ON tablesql.id = tablesql_text.table_id