Create tables and insert data
create table t select_info;
MariaDB [test]> create table t2 select user,host password from mysql.user where user='root'; MariaDB [test]> select * from t2; +------+-------------+ | user | password | +------+-------------+ | root | 127.0.0.1 | | root | ::1 | | root | localhost | | root | zabbix\_ser | +------+-------------+ 4 rows in set (0.00 sec)
create table t as select_info
MariaDB [test]> create table t3(user char(20),host char(20),password char(50)) as select user,host,password from mysql.user where user='root'; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from t3; +------+-------------+-------------------------------------------+ | user | host | password | +------+-------------+-------------------------------------------+ | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | root | zabbix\_ser | | | root | 127.0.0.1 | | | root | ::1 | | +------+-------------+-------------------------------------------+ 4 rows in set (0.00 sec)
These statements retrieve data and create a new table according to the target field. The table must not already exist unless the or replace or if not exists clause is used.
Create table structure only, do not insert data:
create table t1 like t2 --Create the exact same table structure create table tbl_name1 select v1,v2,v3 from t2 where 1=0; # where false. -- You can filter some fields as the structure of a new table
Duplicate key value
Ignore all the wrong rows with ignore keyword, so that insert operation continues to insert the following data. Using insert... on duplicate key update, the row update with duplicate values is the new value. Replace insert into statements with replace into statements, replacing lines with duplicate values with new lines
ignore
Preparing the test environment
MariaDB [test]> insert into t4 values
-> (1,'man','zs'),
-> (2,'man','ls'),
-> (3,'man','ww'),
-> (4,'women','xh'),
-> (5,'women','xl'),
-> (6,'women','xb'),
-> (7,'man','ld'),
-> (8,'women','hj'),
-> (9,'men','lk');
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0
MariaDB [test]> select * from t4;
+----+-------+------+
| id | sex | name |
+----+-------+------+
| 1 | man | zs |
| 2 | man | ls |
| 3 | man | ww |
| 4 | women | xh |
| 5 | women | xl |
| 6 | women | xb |
| 7 | man | ld |
| 8 | women | hj |
| 9 | men | lk |
+----+-------+------+
9 rows in set (0.00 sec)
insert data
MariaDB [test]> insert into t4 values(5,'women','lw'),(10,'man','sg'); ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY' MariaDB [test]> select * from t4 where id=5 or id=10; +----+-------+------+ | id | sex | name | +----+-------+------+ | 5 | women | xl | +----+-------+------+ 1 row in set (0.00 sec)
Use ignore keyword
MariaDB [test]> insert ignore into t4 values(5,'yao','lll'),(10,'ttt','666'); Query OK, 1 row affected, 1 warning (0.00 sec) Records: 2 Duplicates: 1 Warnings: 1 MariaDB [test]> select * from t4 where id=5 or id=10; +----+-------+------+ | id | sex | name | +----+-------+------+ | 5 | women | xl | | 10 | ttt | 666 | +----+-------+------+ 2 rows in set (0.00 sec)
Modify duplicate value records using on duplicate key update clause
In two cases:
1. Inserted records do not have key-value conflicts
It doesn't matter whether you take it or not.
2. Inserted records have key-value duplication conflicts
The on duplicate key update clause updates the original records in the table
test
MariaDB [test]> select * from t5;
±—±-----±-----+
| id | sex | name |
±—±-----±-----+
| 1 | xx | qq |
| 2 | xx | ww |
| 3 | yy | ee |
±—±-----±-----+
3 rows in set (0.00 sec)
Inserted records do not have key value conflicts
MariaDB [test]> insert into t5 values(4,'xx','rr'),(5,'yy','tt'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
Inserted records have key-value duplication conflicts
MariaDB [test]> insert into t5 values(4,'xx','rrrr'),(6,'yy','yy') on duplicate key update name='rrrr'; Query OK, 3 rows affected (0.00 sec) Records: 2 Duplicates: 1 Warnings: 0 MariaDB [test]> select * from t5; +----+------+------+ | id | sex | name | +----+------+------+ | 1 | xx | qq | | 2 | xx | ww | | 3 | yy | ee | | 4 | xx | rrrr | | 5 | yy | tt | | 6 | yy | yy | +----+------+------+ 6 rows in set (0.00 sec)
You can also use the function VALUES (col_name) to refer to insert partial column values in the update clause
MariaDB [test]> insert into t5 values(4,'xx','rrrr') on duplicate key update name=concat('yy',values(id));
Query OK, 2 rows affected (0.08 sec)
MariaDB [test]> select * from t5;
+----+------+------+
| id | sex | name |
+----+------+------+
| 1 | xx | qq |
| 2 | xx | ww |
| 3 | yy | ee |
| 4 | xx | yy4 |
| 5 | yy | tt |
| 6 | yy | yy |
+----+------+------+
6 rows in set (0.00 sec)
PS: VALUES function only in insert... The on duplicate key update statement makes sense, while the others return NULL.
Principle of insert-on duplicate key update execution
Insert a new row to determine whether there is a key value conflict with the existing record. If so, update the old behavior new row (trigger before update trigger, trigger after update)
replace into statement,
When there is no key value conflict, it is completely equivalent to insert in. When there is a key value conflict, the new row is replaced by the old row.
test
MariaDB [test]> select * from t5; +----+------+------+ | id | sex | name | +----+------+------+ | 1 | xx | qq | | 2 | xx | ww | | 3 | yy | ee | | 4 | xx | yy4 | | 5 | yy | tt | | 6 | yy | yy | +----+------+------+ 6 rows in set (0.00 sec) MariaDB [test]> replace into t5 values(4,'yy','xx'); Query OK, 2 rows affected (0.00 sec) MariaDB [test]> select * from t5; +----+------+------+ | id | sex | name | +----+------+------+ | 1 | xx | qq | | 2 | xx | ww | | 3 | yy | ee | | 4 | yy | xx | | 5 | yy | tt | | 6 | yy | yy | +----+------+------+ 6 rows in set (0.00 sec)
Replinto Execution Principle
Insert new rows to determine whether there is a conflict (trigger before insert trigger), delete old rows if there is, and insert new rows (before delete - > after delete - > after insert)