MySql cheat sheet

MySql cheat sheet

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;