Кодировка, кракозяблы

admin    mysql     mysql, utf8, кодировка, конвертирование

Mysql поддерживает много кодировок и это нередко является головной болью для программистов. Самая частая проблема – кракозяблы вместо русского текста. Это происходит из за того, что текст либо лежит на сервере, либо отдается клиенту в неверной кодировке. Последнее(а иногда и первое) решается проще всего. Устанавливаем кодировку соединения (в utf8 в примере) сразу после установления соединения

mysql_set_charset('utf8');
// или mysql_query('SET NAMES "utf8"');

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

mysql_query("SHOW VARIABLES LIKE 'char%'" );

/*
character_set_client: latin1
character_set_connection: latin1
character_set_database: utf8
character_set_filesystem: binary
character_set_results: latin1
character_set_server: cp1251
character_set_system: utf8
character_sets_dir: \usr\local\mysql-5.1\share\charsets\
*/

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

1. character_set_client – кодировка, в которой данные будут поступать от клиента
2. character_set_connection – по умолчанию для всего, что в рамках соединения не имеет кодировки
3. character_set_database – кодировка по умолчанию для баз
4. character_set_filesystem – кодировка для работы с файловой системой (LOAD DATA INFILE, SELECT … INTO OUTFILE, и т.д.)
5. character_set_results – кодировка, в которой будет выбран результат
6. character_set_server – кодировка, в которой работает сервер
7. character_set_system – идентификаторы MySQL, всегда UTF8
8. character_sets_dir – папка с кодировками

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

В идеальном варианте, нам следует далее

InnoDB vs MyISAM

admin    mysql     mysql, оптимизация

После очередного топика на Хабре в стиле "Но ведь все сколько-нибудь компетентные вебдевелоперы знают, что надо использовать движок InnoDB" решил написать обзор.
Впервые с проблемами блокировки я столкнулся сравнительно давно, еще лет 8 назад. Примитивный счетчик загрузок, в котором через UPDATE обновлялось поле cnt , отлично работал. Когда на проекте увеличилась посещаемость,  mysql начал дико тормозить. Просмотр процессов показал, что висит множество SELECT запросов к этой таблице. Так я узнал, что UPDATE на время исполнения лочит всю MyISAM таблицу и самое быстрое решение проблемы, сковертировать таблицу в InnoDB. Но поскольку мне нужен был полнотекстовый поиск, пришлось пойти другим путем. Вынести частые обновления в промежуточную таблицу типа MEMORY. А основную обновлять изредка по cron-у.

Разбираемся с чем есть MyISAM.

Не поддерживает транзакции и с этим связаны его основные недостатки и преимущества

1. В большинстве случаев он быстрее, так как нет расходов на транзакции
2. Занимает меньше дискового пространства
3. Меньше расход памяти на обновления
4. Полнотекстовый индекс
5. Быстрый INSERT, SELECT

InnoDB
 

1. Поддержка транзакций
2. Построчная блокировка. UPDATE не блокирует всю таблицу.
3. Отлично ведет себя при смешанной нагрузке (insert|select|update|delete)

Подведем итог, ответив на вопрос в заголовке, MyISAM или InnoDB?

Если прочитав все выше, вы так и не решили, что выбрать, то лучше первое. В остальном, универсального решения нет, все зависит от задачи.
Если у вас приложение типа OLAP то выбирайте MyISAM, если OLTP, то – InnoDB.

Select с выборочной сортировкой

admin    mysql     mysql, tricks

SELECT * FROM myTable WHERE id IN (17,2,33,19)

Как отсортировать таблицу по списку в IN? По умолчанию mysql отсортирует список по id. Раньше,  делал  средствами php. Оказывается есть сразу 2 способа сделать это встроенными средствами mysql

#решение 1
SELECT * FROM myTable WHERE id IN (17,2,33,19) ORDER BY FIND_IN_SET(id, 17,2,33,19)
#решение 2
SELECT * FROM myTable WHERE id IN (17,2,33,19) ORDER BY FIELD(id, 17,2,33,19)

Error while sending QUERY packet

admin    mysql, php     debug, mysql, php, webdev

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

Вылечить не удалось. Но "подпорку" можно подставить. В качестве подпорки mysql_ping(). Банально пингуем соединение и если оборвалось, соединяемся заново.

if (!mysql_ping ($conn)) {
   mysql_close($conn);
   $conn = mysql_connect('localhost','user','pass');
   mysql_select_db('db',$conn);
}

LEFT JOIN и INNER JOIN

admin    mysql     mysql

Сегодня долго объяснял разницу между MySQL запросами с LEFT JOIN и INNER JOIN, потом вспомнил, что сам долго разбирался и написал небольшой help.

Допустим есть 2 таблицы alpha и beta.

id nameA    /alpha/
-- ----
1  mama
2  papa
3  kasha
________

id nameB   /beta/
-- ----
2  Pirate
8  Monkey

Запрос c LEFT JOIN вернет все 3 строки из таблицы alpha обьединив по id с таблицей beta недостающие значения заполнив NULL

SELECT * FROM alpha LEFT JOIN beta USING (id)
------------------
1 mama NULL NULL
2 papa  2 Pirate
3 kasha NULL NULL

Запрос c INNER JOIN вернет 1 строку c id=2 который есть в обеих таблицах

SELECT * FROM alpha INNER JOIN beta USING (id)
---------------
2 papa  2 Pirate

mysql server has gone away

admin    mysql, php     mysql, php

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

Выяснил, если процесс выполнялся более 30 сек, соединение с mysql отваливалось с ошибкой  “MySQL error: MySQL server has gone away”. При этом скрипт успешно отрабатывал и выполнял свои действия, только не заносил в базу результат

Проблема в настройках mysql сервера у хостера  wait_timeout = 30. Лечится очень просто. Либо заново mysql_connect() в скрипте, либо перед началом длительных действий выполнить SQL

SET SESSION wait_timeout = 1800

mysql узнать размер таблицы

admin    mysql     mysql

Иногда требуется узнать физический размер mysql таблицы на диске. Нашел такой вот простенький запрос.

SHOW TABLE STATUS LIKE 'fa_stat_download'

или сложнее

SELECT
    table_name AS table_name,
    engine,
    ROUND(data_length/1024/1024,2) AS total_size_mb,
    table_rows
FROM
    information_schema.tables
WHERE  table_schema = 'gim' and
   table_name like 'test%'
   ORDER BY 3;

Выборка из mysql по дням, месяцам, годам

admin    mysql     mysql

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

SELECT SUBSTRING_INDEX(created,' ',1) as day, SUM(cnt) as cnt
FROM fa_stat_download
WHERE created BETWEEN FROM_UNIXTIME($ux0) AND FROM_UNIXTIME($ux)
GROUP BY day

Решение легко поправить для получения результата по месяцам и годам.