SQL Fiddle – еще один онлайн инструмент, который можно добавить в закладки. Неплохая песочница для SQL.

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

Если уже мы решились заняться серверным программированием, то без базы не обойтись. Поэтому разберем как должна работать связка node.js и mySQL.

Для начала поставим модуль работы с базой:

npm install mysql

Получаем объект модуля:

[javascript]var mysql = require(‘mysql’);[/javascript]

Создаем соединение:

[javascript]var client = mysql.createClient();
client.host=’127.0.0.1′;
client.port= ‘3306’;
client.user=’someuser’;
client.password=’userpass’;
client.database=’node’;[/javascript]

Теперь можем посылать запросы к базе используя метод  client.query(), который имеет следующий синтаксис:

[javascript]client.query(query, [params, callback])[/javascript]

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

[javascript]client.query(‘SELECT * FROM users’, function(error, result, fields){
console.log(result);
}); [/javascript]

чтобы завершить соединение делаем:

[javascript]client.end();[/javascript]

Более сложные примеры потом.

UPD: по просьбам трудящихся обновляю пост.

Подключаемся:

[javascript]
var connection = mysql.createConnection({
host : ‘localhost’,
user : ‘root’,
password : ‘secret’
});
[/javascript]

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

[javascript]
var query = connection.query(‘INSERT INTO users SET ?’, user, function(err, result) {
console.log(err);
console.log(result);
});
[/javascript]

Где user – это объект с данными.

Обновление:

[javascript]
var query = connection.query(‘UPDATE users SET ? WHERE id = 9’, user, function(err, result) {
console.log(err);
console.log(result);
});
[/javascript]

Можно также переопределить метод query:

[javascript]
connection.config.queryFormat = function (query, values) {
if (!values) return query;
return query.replace(/\:(\w+)/g, function (txt, key) {
if (values.hasOwnProperty(key)) {
return this.escape(values[key]);
}
return txt;
}.bind(this));
};
[/javascript]

и тогда можно будет использовать следующий синтаксис:

[javascript]
connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" });
[/javascript]

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

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

Я решил написать этот пост, так как осознал: люди с большим опытом работы с MySQL порой не знают, что  база имеет встроенную возможность профилирования запросов. Появилась она еще с MySQL 5.0.37.

В MySQL есть 2 sql–команды для работы с профайлером:

SHOW PROFILE

SHOW PROFILES

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

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

mysql> set profiling=1;

Включили? Теперь выполните несколько произвольных запросов … и посмотрим что нам скажет профайлер:

mysql> show profiles;

Мы увидим нумерованный список запросов. По умолчанию профайлер хранит последние 15 запросов(можно настроить set profiling_history_size = 50). Чтобы посмотреть подробную информацию о каком либо из них:

mysql> show profile for query 5; // 5 - номер в списке

И мы должны увидеть табличку с двумя колонками:

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| checking query cache for query | 0.000054 |
| Opening tables                 | 0.000025 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000036 |
| init                           | 0.000014 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000015 |
| preparing                      | 0.000011 |
| executing                      | 0.000005 |
| Sending data                   | 0.000117 |
| end                            | 0.000008 |
| query end                      | 0.000003 |
| freeing items                  | 0.000031 |
| storing result in query cache  | 0.000006 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+

Duration – это длительность выполнения операции(как подсказывает кэп), а Status – сама операция. Полный список возможных операций можно посмотреть тут.

Информацию из профайлера можно получить не только командами SHOW PROFILE, но и обычным SELECT запросом к таблице PROFILING из INFORMATION_SCHEMA, т.е.:

mysql> select STATE, FORMAT(DURATION, 6) as DURATION 
              from INFORMATION_SCHEMA.PROFILING where QUERY_ID = 5;

Успешного вам анализа и оптимизации!

Недавно обнаружил для себя интересную альтернативу Replace в MySQL.

Суть: если у нас после добавления новой записи возникает дублирование( по первичному или уникальному ключу) – то мы обновляем старую запись, причем правило для обновления задаем сами. Появилось свойство еще в MySQL 4.1.0, но раньше как-то не обращал внимания.

Чтобы не уходить далеко от практики, разберем пример. Создадим таблицу:

CREATE TABLE `test` (
`a` INT,
`b` INT,
`c` INT,
PRIMARY KEY ( `a` )
)

Добавим одну запись в таблицу:

INSERT INTO `test` (`a` ,`b` ,`c`) VALUES (1, 1, 1);

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

INSERT INTO `test` (`a` ,`b` ,`c`) VALUES (1, 2, 3);

Получим ошибку:

#1062 – Duplicate entry ‘1’ for key ‘PRIMARY’

и запись не будет добавлена.

А теперь мы хотим построить такой запрос, который при совпадении индекса обновит информацию в старой записи:

INSERT INTO test (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

В итоге получим таблицу:

Что для нас может быть интересно? что во второй части, а именно в UPDATE мы можем прописать не обязательно инкремент значения поля, а любые действия, которые захотим – как одно, так и несколько (через запятую).

INSERT INTO test (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE a=300, b=300, c = a+b;

1. Движок(engine) обеих связываемых таблиц должен быть InnoDB.

2. Связываемые поля должны быть проиндексированы и иметь один тип ( размер и знак должен быть один )

Из основного это все.

Теперь синтаксис взятый из официального мануала:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

Где:

symbol - имя ключа
index_name - имя поля в таблице, которое мы хотим сделать ключом tbl_name - таблица, с которой осуществляем связывание index_col_name - имя поля, с которым связываем наш ключ reference_option - какое действие осуществляем в случае чего

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

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