Лайфхаки SQL. Команды SQL для автоматизирования работы

Чтобы дату из *nix формата перевести в datetime, во первых нужно тип поля created_at перевести в тип text и потом достаточно выполнить команду:

UPDATE `content` SET `created_at`=DATE_FORMAT( FROM_UNIXTIME(`created_at`) , '%Y-%m-%d %T' )

И после выполнения не забываем у поля created_at поменять тип на datetime. Теперь зальём CSV файл и переименуем его:

RENAME TABLE `table_new`.`TABLE 22` TO `table_new`.`images`;

Теперь давайте рассмотрим как можно из одной таблицы MySQL перенести данные в другую таблицу исходя из общих черт. В нашем случае в двух таблицах у нас имеются одинаковые ключевые слова, а нам нужно из перенести пути картинок. В первую очередь нужно изменить путь с files/8908097097086.jpg на uploads/images/8908097097086.jpg, для этого выполняем команду:

UPDATE `images` SET `preview` = REPLACE(`preview`, "images/", "uploads/images/");

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

UPDATE content b, images a
SET b.image=a.preview
WHERE a.keys=b.keywords;

И следующим шагом мы добавим данные из одной таблицы в другую поле в поле:

INSERT INTO `sone_articles`(`category_id`, `title`, `description`, `keywords`, `slug`, `introtext`, `content`, `image`, `date`, `created_at`, `updated_at`) SELECT '1', `title`, `description`, `keywords`, `alias`, `description`, `content`, `image`, `created_at`, `created_at`, `updated_at` FROM `content`;

Удаляем пустые строки из таблицы импортированной через CSV со значением NULL у столбца COL 3:

DELETE FROM `TABLE 26` WHERE `COL 3` IS NULL;

Рандомное обновление числового поля:

UPDATE `sone_articles` SET `view_count`=RAND( ) * (10000 - 673 ) + 568

Бывают случаи когда при парсинге начинают генерится картинки с названиями типа none_1.png, none_2.png, none_3.png и т.д. Для того чтобы изменить массово нужно использовать команду:

UPDATE `sone_articles` SET image = 'uploads/images/none.png' WHERE image LIKE 'uploads/images/none\_%'

Удаление пустых строк, если у поля стоит значение NULL:

DELETE FROM `book_genre` WHERE `genre` IS NULL

Удаление пустых строк, если не стоит ничего:

DELETE FROM `book_genre` WHERE `genre` = ''

Удаление дублей записей в таблице:

DELETE t1 FROM `sone_books` t1, `sone_books` t2 WHERE t1.genre=t2.genre AND t1.id < t2.id

Обновление таблиц с JOIN:

UPDATE sone_books t1 JOIN sone_authors t2 ON t1.author = t2.author_name SET t1.author_id = t2.id

Выборка всех жанров в единственном экземпляре:

SELECT DISTINCT `genre` FROM `book_genre`

Удаление или добавление чего-то в начале и конце поля:

UPDATE `table` SET `column` = CONCAT( 'чего-то спереди ', `column` )

Сверка строк без пробелов. Используем TRIM:

UPDATE book_genre t1 JOIN sone_genres t2 ON TRIM(t1.genre) == TRIM(t2.name) SET t1.genre_id = t2.id

Преображаем тип данных datetime в unix time:

UPDATE sone_articles SET publishedon=UNIX_TIMESTAMP(STR_TO_DATE(published_at, '%Y-%m-%d %H:%i:%s'));

Очистка таблицы:

TRUNCATE TABLE table_name;

Сброс AUTO_INCREMENT:

ALTER TABLE table_name AUTO_INCREMENT = 1

Как сбросить auto_increment

SSH

Импорт дампа в базу данных:

mysql -h localhost -P 3306 -u root -p database < dump.sql

Экспорт дампа базы данных:

mysqldump -h localhost -P 3306 -u root -p database --no-tablespaces > dump.sql