Регистрация Войти
Войти через VK Войти через FB Войти через Google Войти через Яндекс

Поиск по сайту

Наш чат в Telegram для обмена идеями, проектами, мыслями, людьми в сфере ИТ г.Ростова-на-Дону: @it_rostov

Вопросы и ответы по MySQL и MySql+PHP

Как узнать следующее значение AUTO_INCREMENT из таблицы в MySQL?

$res=mysql_query("SHOW TABLE STATUS LIKE 'tbl'");
if($row=mysql_fetch_assoc($res))
    return intval($row['Auto_increment']);
else
    return 1;

Обратите внимание, в интернете часто встречается ошибочный способ определения номера(id) записи, которая будет добавлена:

SELECT max(id)+1 as q FROM tbl

Это неверно! Т.к. если последняя запись была удалена, то id значение добавленной записи не будет соответствовать MAX+1!

Кроме того, при большом количестве одновременных запросоов в базу от разных клиентов id может стать больше, чем только что полученный функцией SHOW TABLE STATUS LIKE 'tbl' т.к. мог проскочить INSERT. Например id: 1,2,3,4 Удалил 3. Следующее добавляемое 5, а не 3.

Как установить начальное значение для поля с auto increment в MySQL?

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

ALTER TABLE tab SET AUTO_INCREMENT=1;

После этого, при добавлении, следующая запись будет имень номер самого большого id + 1

Если нужно при создании таблицы:

CREATE TABLE tab (id INT UNSIGNED AUTO_INCREMENT,....PRIMARY KEY(id)) AUTO_INCREMENT=123;

Как использовать свободные id записей?

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

Если Вы используете id для нумерации, когторую показываете пользоветлю, то "дырки" в нумерации - не очень красиво.

Есть два варианта:

первый:

После каждого удаления сбрасывать AUTO_INCREMENT в 1 с помощью команды:

alter table `tbl` auto_increment=1

Пример:

mysql_query("DELETE FROM `tbl` WHERE id='".$id."' LIMIT 1");
if(mysql_affected_rows()>0)
    mysql_query("alter table `tbl` auto_increment=1;");

второй:

После добавления записи для получения её id вместо стандартной функции mysql_insert_id() использовать следующую:

/* получить id только что добавленной записи и
   по возможности переместить её в свободный id */
function GetInsertId($tbl){
global $insert_id;
$id0=mysql_insert_id(); if($id0<1) return $id0;
$id_from=(isset($insert_id[$tbl])?$insert_id[$tbl]:1);

for($id=$id_from; $id<$id0; $id++){
   $result = mysql_query('SELECT id from '.$tbl.' WHERE id='.$id.' LIMIT 1');
   if(mysql_num_rows($result)==0){
    mysql_query('UPDATE IGNORE '.$tbl.' SET id='.$id.' WHERE id='.$id0.' LIMIT 1');
    if(mysql_affected_rows()<1)continue; // уже заняли
        $insert_id[$tbl]=$id+1;
    return $id;    // id - свободен
   }
}
$insert_id[$tbl]=$id;
return $id0;
}

Она ищет "дырку" в нумерации (свободные id номера) и пытается занять первый свободный, если успешно, то последняя добавленная запись будет иметь id "дырки" и возвращен номер. На случай частого использования данные кешируются в переменную памяти.

Вывод из связанных баз

Вывести все поля из KATEG и поле name из GR. Осторожно с одинаковыми именами полей. Они берутся из второй базы.

$query = mysql_query("SELECT *,gr.name as name FROM kateg,gr WHERE kateg.gr=gr.id ORDER by name");

Проверка наличия записи по условию

Если есть хотя бы одна запись удовлетворяющая условию:

if(mysql_num_rows(mysql_query('SELECT * from kateg WHERE gr='.$del.' LIMIT 1'))>0)
    die('Не удаляю, т.к.....!');

Удаление в связанных базах

Иногда хочется написать запрос типа такого:

mysql_query("DELETE FROM price,supplier WHERE price.id=supplier.tovar and price.gr=".$del);

Но он работать не будет, вот эквивалентный работающий код:

$result=mysql_query('SELECT * from price WHERE price.gr='.$del);
while ($row = mysql_fetch_assoc($result)){ // удаляю во второй базе множество записей
    mysql_query('DELETE FROM supplier WHERE supplier.tovar='.$row['id']);
}
mysql_query('DELETE FROM price WHERE price.gr='.$del); // удаляю в первой базе

Количество записей

Подсчитать количество записей, удовлетворяющих условию

$q=mysql_query('SELECT count(*) as counter from price WHERE gr='.$row['id']);
$data=mysql_fetch_assoc($q);
$counter=($data?intval($data['counter']):0);

Получение ключа только что добавленной записи

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

mysql_insert_id()

mysql_query("INSERT INTO mytable (product) values ('kossu')");
echo "id последней добавленной записи ".mysql_insert_id();

Перенумеровать поле id UNIQUE AUTO_INCREMENT в mysql

В 99 процентах случаях это делать не нужно. Уникальный ключ для того и создан, чтобы при удалении записи оставалась "дырка" в нумерации. Но, иногда приходится. Например, я создавал сайт и набирал справочник для клиента на локальной машине, естественно, было много правок. Хотелось при "выкладывании вживую" чтобы все выглядело красиво. Вот для такого случая и сделан следующий скриптик:

$q=sql('SELECT count(*) as counter from gr');
$data=mysql_fetch_assoc($q); $counter=($data?intval($data['counter']):0);
for($id=1; $id<=$counter; $id++){ // перенумеровываю все id
    $result = sql('SELECT * from gr WHERE id='.$id.' LIMIT 1');
    if(mysql_num_rows($result)==0) {// id - свободен
        $result = sql('SELECT MAX(id) as id from gr');
        if (($row = mysql_fetch_assoc($result)) && ($id<$row['id'])){
            sql('UPDATE gr SET id=LAST_INSERT_ID('.$id.') WHERE id='.$row['id'].' LIMIT 1');
            // то же сделать во всех связанных базах!
            sql('UPDATE price SET gr='.$id.' WHERE gr='.$row['id']);
            sql('UPDATE kateg SET gr='.$id.' WHERE gr='.$row['id']);
        }
        else break;
    }
}

Преобразование IP адреса для записи в базу MySql

function int2ip($i)
{ // INET_NTOA(3520061480)
$d[0]=(int)($i/256/256/256);
$d[1]=(int)(($i-$d[0]*256*256*256)/256/256);
$d[2]=(int)(($i-$d[0]*256*256*256-$d[1]*256*256)/256);
$d[3]=$i-$d[0]*256*256*256-$d[1]*256*256-$d[2]*256;
return "$d[0].$d[1].$d[2].$d[3]";
}


function ip2int($ip)
{ // INET_ATON("209.207.224.40")
$a=explode(".",$ip);
return $a[0]*256*256*256+$a[1]*256*256+$a[2]*256+$a[3];
}

Как получить список id записей в строку по условию из MySql

Чтобы выбрать список записей в виде строки id кодов воспользуется функцией MySql GROUP_CONCAT

SELECT GROUP_CONCAT(DISTINCT id SEPARATOR ',') AS plan_id
    FROM table
    WHERE `Group` = 1

Найти в связанных таблицах потерянные записи
(записи не имеющие значение в справочнике)

SELECT * FROM tovar
    LEFT JOIN category_link
        ON tovar.id=category_link.tovar
    WHERE category_link.tovar IS NULL

Если нужно найти и удалить потерянные записи:

SELECT swdist_head.dist_id
    FROM swdist_head LEFT JOIN swdist_item
    ON swdist_head.dist_id = swdist_item.dist_id
    WHERE swdist_item.dist_id IS NULL;

Соответствующее предложение DELETE будет таким:

DELETE swdist_head
    FROM swdist_head LEFT JOIN swdist_item
    ON swdist_head.dist_id = swdist_item.dist_id
    WHERE swdist_item.dist_id IS NULL;

Запрос, определяющий потомков без родителей, выглядит так:

SELECT swdist_item.dist_id
    FROM swdist_item LEFT JOIN swdist_head
    ON swdist_item.dist_id = swdist_head.dist_id
    WHERE swdist_head.dist_id IS NULL;

Соответствующее предложение DELETE удаляет их:

DELETE swdist_item
    FROM swdist_item LEFT JOIN swdist_head
    ON swdist_item.dist_id = swdist_head.dist_id
    WHERE swdist_head.dist_id IS NULL;

Сколько сообщений было отправлено в течение каждого часа дня?

SELECT HOUR(t) AS hour, COUNT(HOUR(t)) AS count FROM mail GROUP BY hour;

Преобразование TIMESTAMP в DATETIME и обратно

SELECT DATE_FORMAT(t_create,'%Y-%m-%d %T') FROM tbl; // CCYYMMDDhhmmss -> CCYY-MM-DD hh:mm:ss

SELECT DATE_FORMAT(dt,'%Y%m%d%H%i%s'), dt+0 FROM tbl;  // CCYY-MM-DD hh:mm:ss -> CCYYMMDDhhmmss

MyISAM или InnoDB ?

MyISAM быстрее InnoDB - сомнительно. Они предназначены для разных видов приложений:

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

Mysql как игнорировать ошибки при загрузке дампа?

Используйте ключ "-f" - он позволяет продолжать даже если в процессе произошла ошибка. Например:

mysql.exe --default-character-set=cp1251 -f -u root -p --database=base1 < !.!

Долгий MySQL запрос и на что его заменить?

Данный запрос на таблице из 100тыс.записей выполняется более 10 секунд

SELECT user_id FROM `user_spy` WHERE key in (
    SELECT key FROM `user_spy` WHERE `user_id` = 32602)
AND user_id != 32602

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

SELECT group_concat(distinct key) FROM `user_spy` WHERE `user_id` = 32602
+
SELECT user_id FROM user_spy WHERE key in ('xxxx','yyy') AND user_id != 32602

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

Запрос для конвертации базы:

ALTER TABLE `db_name`.`table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Если у вас MySQL сервер версии 5+ вы счастливчик! который сможет выполнить вот такой запрос:

SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as sqlcode
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'db_name'
ORDER BY 1

Замените! db_name на имя вашей Базы Данных.

После выполнения запроса получаем ответ SQL в стиле:

ALTER TABLE `bla_bla`.`bla_brands` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_models` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_modify` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_params` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_param_groups` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_param_names` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_submodels` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`province` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Копируем его, вставляем в форму запроса SQL и выполняем, результат - полностью изменённая кодировка всех таблиц на utf8 - наслаждаемся сэкономленным временем!

А если нужно utf8_general_ci таблицы конвертировать в utf8mb4_general_ci для того, чтобы смайлики Emoji сохранялись:

ALTER TABLE xxx MODIFY COLUMN yyy COLLATE utf8mb4_general_ci, ... ;

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

mysqli_query("set names 'utf8'");

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

Хорошая статья про использование индексов
Все функции MySql
Примеры использования PHP и MySql

Популярные вопросы по MySQL и ответы на них. FAQ: MySQL, PHP