Войти через VK Войти через FB Войти через Google Войти через Яндекс
Поиск по сайту
Вопросы и ответы по 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