Last active
July 22, 2024 11:54
-
-
Save Korveld/e6b54b60103d3fb0abaaa6113dfa625a to your computer and use it in GitHub Desktop.
MySql cheat sheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT * FROM `wp_posts` ORDER BY `wp_posts`.`post_date` DESC | |
Drop column | |
ALTER TABLE wp_posts DROP COLUMN Created; | |
Add column | |
ALTER TABLE wp_posts ADD COLUMN post_author INT DEFAULT 1 AFTER ID; | |
Rename column | |
ALTER TABLE wp_posts CHANGE COLUMN LastEdited post_date DATETIME; | |
Add column and copy values | |
ALTER TABLE wp_posts ADD COLUMN post_date_gmt DATETIME AFTER post_date; | |
UPDATE wp_posts SET post_date_gmt = post_date; | |
OR | |
UPDATE wp_posts SET guid = CONCAT('http://rytasvilnius.loc/?p=', ID); | |
Move column | |
ALTER TABLE wp_posts MODIFY COLUMN post_content MEDIUMTEXT AFTER post_date_gmt; | |
Rename table | |
RENAME TABLE wp_posts TO wp_posts; | |
Change NULL to empty string | |
UPDATE wp_posts SET post_content = '' WHERE post_content IS NULL; | |
Copy tables between databases | |
USE staginglab_rytasvilnius; | |
INSERT INTO staginglab_rytasvilnius.wp_posts | |
SELECT * | |
FROM bcnius_bclr2.wp_posts; | |
INSERT INTO staginglab_rytasvilnius.wp_posts | |
SELECT * | |
FROM bcnius_bclr2.wp_posts | |
WHERE ParentID = 20; | |
Change IDs | |
USE your_database_name; | |
SET foreign_key_checks = 0; | |
CREATE TEMPORARY TABLE temp_wp_posts AS SELECT * FROM wp_posts; | |
UPDATE temp_wp_posts SET ID = ID + (853 - (SELECT MIN(ID) FROM wp_posts)); | |
TRUNCATE TABLE wp_posts; | |
INSERT INTO wp_posts SELECT * FROM temp_wp_posts; | |
DROP TABLE temp_wp_posts; | |
ALTER TABLE wp_posts AUTO_INCREMENT = (SELECT MAX(ID) FROM wp_posts) + 1; | |
SET foreign_key_checks = 1; | |
#1067 - Invalid default value for 'post_date'" when trying to reset AI after backup | |
ALTER TABLE `wp_posts` | |
CHANGE `post_date` `post_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
CHANGE `post_date_gmt` `post_date_gmt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
CHANGE `post_modified` `post_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
CHANGE `post_modified_gmt` `post_modified_gmt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP; | |
Copy table from one DB to another | |
-- Create the File table in the target database by copying the structure from the source database | |
CREATE TABLE staginglab_rytasvilnius.File LIKE bcnius_bclr2.File; | |
-- Copy the data from the File table in the source database to the target database | |
INSERT INTO staginglab_rytasvilnius.File SELECT * FROM bcnius_bclr2.File; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment