Insert non-repeated records ~~~ Evaluate a mysql statement ~~~ The table structure is very simple: SQLcode ---------------------------- id | cat | article ---------------------------------- 1abc12aaa23 insert non-repeated records ~~~ Evaluate a mysql statement ~~~
The table structure is simple:
SQL code
--------------------------------id | cat | article-------------------------------- 1 abc 1 2 aaa 2 3 ccc 1--------------------------------
Here, id is the unique primary key, AUTO_INCREMENT, which is useless for inserting records.
Now you need to insert some records. you can insert the cat and article_id of the new record only when they are not present in the original data. For example, records with cat = 'ddd ', article = 1, cat = 'AAA', and article = 1 can be inserted, but cat = "abc ", records with article = 1 and cat = "ccc" and article = 1 are not allowed to be inserted because such records already exist.
How can I write mysql statements?
I am not very familiar with the posts I found on the internet. it seems that all the posts depend on primary keys and are not repeated.
------ Solution --------------------
Associate a unique index on cat and article
Use the replace command during insertion
------ Solution --------------------
Don't you read the manual? How can we make progress?
CREATE [UNIQUE | FULLTEXT] INDEX index_name
ON tbl_name (col_name [(length)],...)
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES (expression ,...),(...),...
Or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT...
Or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name = expression, col_name = expression ,...
The REPLACE function is exactly the same as the INSERT function. except if an old record and a new record have the same value on a UNIQUE or primary key in the table, before the new record is inserted, old records will be deleted. View Section 6.4.3 INSERT syntax.
In other words, you cannot access the value of the old record row from a REPLACE. In some old MySQL versions, you may be able to do this, but this is a Bug and has been corrected.
To use REPLACE, you must have the INSERT and DELETE permissions on the table.
When you use a REPLACE, if the new record row replaces the old record row, mysql_affected_rows () returns 2. This is because the Duplicate Record Row is deleted before the new row is inserted.
This fact makes it easy to determine whether to add a record or REPLACE a record: Check whether the value of the affected record row is 1 (add) or 2 (REPLACE ).
Note that unless you use a UNIQUE index or primary key, the REPLACE command does not feel like it will only execute an INSERT.
------ Solution --------------------
Then you can: insert ignore .............
However, you also need to perform the Union unique index on cat and article.
------ Solution --------------------
The most stupid way is to select whether the value already exists before insertion.
------ Solution --------------------
Check it with select directly.
------ Solution --------------------
Repalce into tablename (id, name) values ('. $ id.', '". $ name."'); or replace into tablename (id, name) select id, name
------ Solution --------------------
According to the idea of the landlord, it is better to select and then insert this method than replace.
------ Solution --------------------
We recommend that you use replace, which is simple and efficient.
------ Solution --------------------
Select is White blind and does not lock the table. it is not atomic at all.
Insert a unique index to view the result.
