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;
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