SELECT * FROM Vzakladke.net

Статьи об автоматизации и программировании

Несколько полезных запросов на MySQL, которые нужно знать или хотя бы о них помнить

 

 

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

Для начала нужно вспомнить, как получить список таблиц и их структуру в MySQL.  


SHOW DATABASES; -- список баз данных
SHOW TABLES [FROM db_name]; --  список таблиц в базе
SHOW COLUMNS FROM table_name [FROM db_name]; -- список столбцов в таблице
SHOW CREATE TABLE table_name; -- показать структуру таблицы в формате "CREATE TABLE"
SHOW INDEX FROM tbl_name; -- список индексов
SHOW GRANTS FOR user [FROM db_name]; -- привилегии для пользователя.

SHOW VARIABLES; -- значения системных переменных
SHOW [FULL] PROCESSLIST; -- статистика по mysqld процессам
SHOW STATUS; -- общая статистика
SHOW TABLE STATUS [FROM db_name]; -- статистика по всем таблицам в базе

Найти все функции и процедуры можно таким способом:

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

SHOW PROCEDURE STATUS WHERE Db = DATABASE() AND Type = 'PROCEDURE' -- не обязательно, но можно и с условием

SELECT * FROM INFORMATION_SCHEMA.ROUTINES -- или все функции и процедуры единовременно

SHOW TRIGGERS -- отобразит список триггеров

 

 

Обновление связанных таблиц (MySQL Inner Join Update) можно выполнить таким запросом:

UPDATE `tblname`
       INNER JOIN `tblname2`
           ON `tblname2`.`num` = `tblname`.`num`
SET `tblname`.`tag` = `tblname2`.`tag`
WHERE `tblname2`.`blabla` = `tblname`.`blabla` AND
`tblname`.`tag` = ''

или так (используя алиасы): 

UPDATE `tblname` a
       INNER JOIN `tblname2` b
           ON a.`num` = b.`num`
SET a.`tag` = b.`tag`
WHERE a.`blabla` = b.`blabla` AND
b.`tag` = ''

 

Определение и уничтожение нежелательных запросов в MySQL делается следующим образом:

Показать все запущенные запросы можно так:

SHOW PROCESSLIST

Или можно показать все запущенные запросы с условием:

SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Query' AND TIME > 10


Убить нежелательный запрос можно по идентификатору, полученному из предыдущих запросов:
KILL "ID";

Но можно и пакетно удалить все нежелательные запросы по условию:

SELECT CONCAT('KILL ',ID,';') FROM   INFORMATION_SCHEMA.PROCESSLIST
WHERE  USER = 'loginweb'  AND COMMAND = 'Query'  AND TIME > 10;

 

 

Автоматическая нумерация строк в mysql тоже тема интересная, нам понадобится переменная

SELECT @row := @row + 1, TABLE_NAME. *
FROM `TABLE_NAME`
JOIN (
   SELECT @row := 0
) AS tmp

 

 

Клонирование таблицы на mysql? Пожалуйста:

-- 1-ый запрос создаст абсолютную копия структуры таблицы "table".
CREATE TABLE `new_table` LIKE `table`

-- 2-ой - вставит в "new_table" данные из "table".
INSERT INTO `new_table` SELECT * FROM `table`

 

 

И в конце концов удалите уже дубликаты из базы!

 

DELETE t1 FROM `my_table` t1, `my_table` t2 

WHERE t1.`my_field` = t2.`my_field` AND t1.`id` > t2.`id`

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

Разберем пример выборки повторяющихся значений, который нам покажет сколько раз повторяется в базе строки по условию выборки:

SELECT
    artist, song, COUNT(*), MAX(id)
FROM
    musics
WHERE 
    artist ='Nirvana'
GROUP BY
    artist, song
HAVING 
    COUNT(*) > 1

 

Теперь попробуем 2-ой вариант с использованием джойна, где оператор MIN или MAX поможет выбирать первую или последнюю строку:

SELECT * FROM musics tblRows
LEFT   JOIN (
   SELECT MAX(id) as kid, artist, song, alblink 
   FROM musics 
   GROUP BY artist, song, alblink 
)   KeepRows ON
   tblRows.id = KeepRows.kid
WHERE
   KeepRows.kid IS NULL
AND
   tblRows.artist ='Nirvana'

 

Теперь когда мы досканально проверили все наши повторения, можем их удалить:

DELETE FROM musics
WHERE id IN 
 (SELECT * 
   FROM (
      SELECT id FROM musics tblRows
      LEFT   JOIN (
         SELECT MAX(id) as kid, artist, song, alblink 
         FROM musics 
         GROUP BY artist, song, alblink 
      )   KeepRows ON
         tblRows.id = KeepRows.kid
      WHERE
         KeepRows.kid IS NULL
      AND
         tblRows.artist ='Nirvana'
      )
  x)

 

Получение размера БД MySQL, используя PHP

Соединение с БД и вычисление:

mysql_select_db( $dbname );
$result = mysql_query( "SHOW TABLE STATUS" );
$dbsize = 0;
while( $row = mysql_fetch_array( $result ) ) {
$dbsize += $row[ "Data_length" ] + $row[ "Index_length" ];
}

 

Функция получение размера БД на PHP:

function formatfilesize( $data ) {
// bytes
if( $data < 1024 ) {
return $data . " bytes";
}
// kilobytes
else if( $data < 1024000 ) {
return round( ( $data / 1024 ), 1 ) . "k";
}
// megabytes
else {
return round( ( $data / 1024000 ), 1 ) . " MB";
}
}

 

И вывод результата:

echo "Размер базы данных составляет "  . formatfilesize( $dbsize );

 

В заключение, перед тем какой выбрать тип таблицы в базе данных, необходимо вспомнить о разнице между MyISAM и InnoDB.


MYISAM:

MYISAM поддерживает таблицы - уровень блокировки
MyISAM разработан для нуждающихся в скорости
MyISAM вовсе не поддерживает внешние ключи, следовательно, мы называем MySQL  с MYISAM является СУБД
MyISAM хранит свои таблицы , данные и индексы в дисковое пространство использованием трех различных отдельных файлов .  ( имя таблицы . FRM , имя таблицы . MYD , имя таблицы . MYI )
MYISAM не поддерживает транзакции .  Вы не можете совершить и откат с MYISAM .  Когда Вы даете команду он ' ы сделали .
MYISAM поддерживает полнотекстовый поиск
Вы можете использовать  MyISAM ,  если таблица является более статические  с большим количеством выбора  и меньше обновления и  удаления .


INNODB:

InnoDB поддерживает ряд - уровень блокировки
InnoDB разработаны для максимальной производительности при обработке больших объемов данных
InnoDB поддерживает внешние ключи, следовательно, мы называем MySQL  с  InnoDB  является СУБД
InnoDB хранит свои таблицы и индексы в табличном
InnoDB поддерживает транзакции .  Вы можете совершить и откат с  InnoDB

 

Дата публикации: 2015-06-12 23:15:49

MySQL, Полезное

1

Отзывы:

Костя
А еще иногда надо почистить таблицу в базе InnoDb, но мешают связи, то можно так: SET FOREIGN_KEY_CHECKS = 0; TRUNCATE table1; SET FOREIGN_KEY_CHECKS = 1; правда это рискованный путь и лучше делать так - удалить записи и сбросить автоинкремент: DELETE FROM table1; ALTER TABLE table1 AUTO_INCREMENT = 1;

Ваше имя:

Ваш e-mail (необязательно):

Сообщение:

Captcha