Решил привести в пример пару SQL запросов с помощью которых массово можно менять даты, копировать из одного поля в другое и прочие хитрости. Пишу кратко и по делу. Для того чтобы нам рандомно поменять даты в выбранном периоде в *nix формате достаточно в нашей таблице content
добавить поле created_at с типом integer и выставить значение 10. После же выполняем команду:
UPDATE `content` SET `created_at` = (
RAND( ) * ( UNIX_TIMESTAMP( '2017-05-09 23:59:59' ) - UNIX_TIMESTAMP( '2015-11-07 23:19:23' ) ) + UNIX_TIMESTAMP( '2015-11-04 00:00:00' )
);
Чтобы дату из *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;
Чтобы удалить пробелы в начале и в конце строк в MySQL, можно использовать функцию TRIM(). Вот пример, как это сделать для поля name в таблице sone_articles базы данных:
UPDATE sone_articles SET `name` = TRIM(`name`);
Этот запрос удалит все пробелы в начале и в конце строк в поле Firma.
Если вам нужно удалить пробелы в начале (LEADING) или в конце (TRAILING) отдельно, вы можете использовать функции TRIM(LEADING) и TRIM(TRAILING) соответственно. Вот примеры:
Удаление пробелов только в начале строки:
UPDATE sone_articles SET `name` = TRIM(LEADING ' ' FROM `name`);
Удаление пробелов только в конце строки:
UPDATE sone_articles SET `name` = TRIM(TRAILING ' ' FROM `name`);
И следующим шагом мы добавим данные из одной таблицы в другую поле в поле:
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
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
Комментарии (0)