To copy with indexes and triggers do these 2 queries:
CREATE TABLE newtable LIKE oldtable; INSERT INTO newtable SELECT * FROM oldtable;
To copy just structure and data use this one:
CREATE TABLE tbl_new AS SELECT * FROM tbl_old;
Category: MySQL
Yii2 Gii – Table Update
ALTER TABLE `tablename` ADD `lock` int(5) NOT NULL, ADD `created_at` date DEFAULT NULL, ADD `created_by` int(11) NOT NULL DEFAULT 0, ADD `deleted_by` int(11) NOT NULL DEFAULT 0, ADD `deleted_at` date DEFAULT NULL, ADD `updated_at` date DEFAULT NULL, ADD `updated_by` int(11) NOT NULL DEFAULT 0 AFTER branch_status;
UPDATE `MySQL_Table` SET `MySQL_Table_Column` = REPLACE(`MySQL_Table_Column`, 'oldString', 'newString') WHERE `MySQL_Table_Column` LIKE 'oldString%';
When Should I use MySQL Search and Replace?
Doing a Search and Replace via SQL could be dangerous if you have links that you’re unaware of which shouldn’t be changed. You should Search and Replace only when you’re convinced that you have to change the text in your Database. You should always perform a Database backup before proceeding with any manual changes.
Add Foreign Key to existing table (MySQL)
To add a foreign key (grade_id) to an existing table (users), follow the following steps:
ALTER TABLE users ADD grade_id SMALLINT UNSIGNED NOT NULL DEFAULT 0; ALTER TABLE users ADD CONSTRAINT fk_grade_id FOREIGN KEY (grade_id) REFERENCES grades(id);
How can I import a database with MySQL from terminal?
Prompt for password:
mysql -u username -p databasename < filename.sql
Enter password directly (not secure):
mysql -u username -pPlainPassword databasename filename.sql
Example:
mysql -u root -p wp_users < wp_users.sql mysql -u root -pPassword123 wp_users --default-character-set=utf8 < wp_users.sql
See also:
4.5.1.5. Executing SQL Statements from a Text File
Repair table from .FRM file MySQL
Error: 1017 Can’t find file! (errno: 2)
mysql> REPAIR TABLE database.postmeta USE_FRM;
Insert/ Update random date in MySQL
UPDATE mytable SET col = CURRENT_TIMESTAMP - INTERVAL FLOOR(RAND() * 14) DAY