Приемы работы с MySQL на PHP

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

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

function nextId($tablename){
if($row=mysql_fetch_assoc(mysql_query("SHOW TABLE STATUS LIKE '$tablename'")))
    return intval($row['Auto_increment']);
else
    return 0;
}

$id=nextId('myTable');
echo $id;

Для получения последней добавленной записи используйте mysql_insert_id().

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

mysql_query("SET NAMES cp1251");
//mysql_query("set names utf8");

function _USER_SetLocaleRus()
{
 $arrLocales = array('ru_RU.CP1251', 'ru_RU.cp1251', 'ru_RU', 'RU');
 foreach ($arrLocales as $strLocaleName)
 {setlocale(LC_ALL, $strLocaleName);
  if (strtolower("qwertyёЁАБГДЯQWERTYZ") == "qwertyёёабгдяqwertyz") break;
 }
}

выбрать с группировкой по дням

SELECT COUNT(`id`), DATE_FORMAT(`time`, '%Y %m %d %H') as dat FROM `tbl` GROUP BY dat ORDER BY dat DESC

Копирование таблицы (включая данные)

CREATE TABLE NewTab SELECT * FROM OldTab;

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

CREATE TABLE NewTab LIKE OldTab;

Добавление записи из одной таблицы в другую (insert & select)

INSERT INTO table1 ( column1, column2, someInt, someVarChar )
SELECT  table2.column1, table2.column2, 8, 'some string etc.'
FROM    table2
WHERE   table2.ID = 7;

Заполнение недостающих данный в table2, из table1 (update & select)

update table2 t2, table1 t1
set t2.pole1=t1.pole1,t2.pole2=t1.pole2
where t1.name=t2.name;
select * from table2;

Копирование всех таблиц из одной базы MySQL в другую

Если вам доступен shell, используйте следующую команду:

mysqldump -q -uЛОГИН -hlocalhost -pПАРОЛЬ БАЗА1 | mysql -uЛОГИН2 -hlocalhost -pПАРОЛЬ2 БАЗА2

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

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

CREATE TABLE db2.table LIKE db1.table;
INSERT INTO db2.table SELECT * FROM db1.table;

Если Вы хотите сделать копирование всех таблиц из одной базы MySQL в другую на PHP используйте следующий пример:

define("UserName","");    // Логин
define("Password","");    // Пароль
define("HostName","localhost");        //  Имя сервера (хост)

define("DBName","db2");        // Имя базы данных приемник
define("DBNameFrom","db1");    // Имя базы данных источник ReadOnly

$link = mysqli_connect(HostName, UserName, Password);

$res = mysqli_query($link, $query);
$r = mysqli_query($link, "SHOW TABLES");
while($row = mysqli_fetch_row($r))
{
    $tbl = $row[0];
    mysqli_query($link, "CREATE TABLE ".db2.".".$tbl." LIKE ".db1.".".$tbl);
    mysqli_query($link, "INSERT INTO ".db2.".".$tbl." SELECT * FROM ".db1.".".$tbl);
}

Оптимизация таблиц

Оптимизация необходима, когда было произведено много изменений в таблице: либо удалена большая часть данных, либо много изменений со строками переменной длины — text, varchar, blob. Дело в том, что удалённые записи продолжают поддерживаться в индексном файле, и при последующей вставке новых записей используются позиции старых записей. Чтобы дефрагментировать файл с данными, используюется команда OPTIMIZE.

OPTIMIZE TABLE `table1`, `table2`…

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

Перестройка данных в таблице

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

ALTER TABLE `table1` ORDER BY `id`

Запись и чтение битовых полей в таблице.

Для тех кто не помнит что такое двоичная система. Слева двоичная система, справа — десятичная.

00001 = 1 - первый флаг
00010 = 2 - второй
00100 = 4 - третий
01000 = 8 - четвёртый
10000 = 16 - пятый
и так далее
01011 = 1+2+8 = 11 - перый, второй и четвёртый

Mysql позволяет выполнять побитовые операции прямо в запросе, максимум 64 «галочки» для BIGINT. В результате мы получим следующий запрос, где n это число, которое получается в результате упаковки значений чекбоксов, которое мы с помощью побитовой операции AND накладываем на поле с данными (предполагается что упакованные данные хранятся в поле variable):

SELECT * FROM `dataTable` WHERE `variable` & n = n

или

SELECT * FROM `dataTable` WHERE `variable` & n != n

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

SELECT * FROM `dataTable` WHERE `variable` & n > 0

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

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

выставить крайний правый бит в 1:
update `dataTable` set `variable`=`variable` | 1

выставить крайний правый бит в 0
update `dataTable` set `variable`=`variable` &~ 1

Так может выглядеть html код с выбором:

<input type="checkbox" name="variable[]" value="1">
<input type="checkbox" name="variable[]" value="2">
<input type="checkbox" name="variable[]" value="3">
<input type="checkbox" name="variable[]" value="4">

Чтобы упаковать все полученные чекбоксы в одно поле используйте следующий PHP-код:

$n=10; //Количество полей
//Результирующее значение
$resultValue=0;
for($i=0;$i<$n;++$i){
    if(!empty($_POST['variable'][$i])){
        $resultValue|=$_POST['variable'][$i]<<$i;
    }
}

Для установки бита в PHP можно испольтзовать:

$number = pow(2, $num % 8); //выставляем единичный бит в значащем байте...

Для проверки в PHP установленного бита

$number = (bool)strlen(trim($needle & $findIn, "\0"));

Примеры работы с битовыми полями на php приведены здесь

update `dataTable` set `variable`="1" // НЕ правильно - будет записана битовая маска соответствующая коду числа 1
update `dataTable` set `variable`=1 // правильно

Если Вы используете MySQL поле типа BIT, то обязательно наступите на грабли: при записи в это поле нельзя заключать число в кавычки:

Пример использования в одном MySQL запросе двух GROUP_CONCAT

Грабли без использования внетренней сортировки.

SELECT
    receiver_id,
    GROUP_CONCAT(template_id ORDER BY id DESC SEPARATOR ',') as tpl,
    GROUP_CONCAT(id ORDER BY id DESC SEPARATOR ',') as id
    FROM notifications
    WHERE `template_id` in ( 52, 51 )
    GROUP BY  receiver_id

Что выберет join запрос наглядно показано на следующей картинке

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

Ищем в базе последние звонки с текущего номера, и если их больше X за Y минут — сбрасываем звонок.

SELECT count(`calldate`) FROM `cdr` WHERE (`src`='79123123123')
    AND (`calldate` BETWEEN NOW() - INTERVAL 5 MINUTE AND NOW())

Интервал указывается прямо в запросе. Запрос отберет количество звонков с текущего номера за последние 5 минут и выведет их в результирующем поле одной строкой.

Полезные ссылки по MySQL:

• Все функции MySql
• Примеры использования PHP и MySql
• Справочник по MySQL на spravkaweb.ru

Читать дальше: Cookie в PHP

.
Прокомментировать/Отблагодарить