SQLite is a C language library that implements a small, fast, self-contained, highly reliable, full-featured SQL database engine. SQLite is the most used database engine in the world. It is built into all mobile phones and most computers, and bundled into countless other applications that people use every day. Here’s how to use the INSERT OR REPLACE command.

The business requirements

In specific services, databases need to be inserted or modified in batches.

  • The current data does not exist, and data is inserted
  • The current data exists and is updated

You can use the INSERT OR REPLACE command of SQlite to perform the above operations. The syntax is as follows:

INSERT OR REPLACE INTO table-name (column-name,...) VALUES (column-value,...)
Copy the code

UNIQUEConstraints orPRIMARY KEYThe constraint

  • UNIQUEConstraint: A unique constraint that prevents two records from having the same value in a particular column
  • PRIMARY KEYConstraint: Constraints uniquely identify each record in a database table.

The same

The PRIMARY KEY is also UNIQUE

The difference between

  • The PRIMARY KEY is non-empty, but UNIQUE can be empty
  • There can only be one primary key, but there can be multiple unique indexes
  • If no aggregate index is specified, the primary key is the aggregate index by default, and the unique index does not have this feature
INSERT OR REPLACE INTO table-name (column-name,...) VALUES (column-value,...)
Copy the code

In colunm-name, if the inserted column-value does not have an existing value, the inserted column does not exist.

Create a table:

CREATE TABLE TARD ( ID INT PRIMARY KEY, name TEXT NOT NULL UNIQUE, age INT NOT NULL , date CHAR(50),Dvalue REAL DEFAULT 300.00);
Copy the code
INSERT INSERT OR REPLACE INTO TARD (ID, name, age, date) VALUES (1, 'xiaoming', 18, // Result ID name age date Dvalue 1 xiaoming 18 call 300 // Update INSERT OR REPLACE INTO TARD (ID, Name, age, date) VALUES (1, 'xiaoming', 118, 'Dvalue ') // Result ID name age date Dvalue 1 xiaoming 118Copy the code

Create table with ID and name UNIQUE, insert only if both values are not present, otherwise update current data

Pay attention to

INSERT OR REPLACE INTO table-name (column-name,…) VALUES (column-value,…) The data already exists. If a row is not filled in during update, the row is empty by default and overwrites the previous data.

INSERT OR REPLACE INTO TARD (ID, name, age, date) VALUES (1, 'xiaoming', 118)
Copy the code

Xiaoming is in the database, and the update operation will be performed. If no date value is entered, the date value will not be retained and will be replaced with NULL

ID	 name	    age	   date       Dvalue
1	xiaoming	118	   NULL	   300
Copy the code

The original linkAbout-longda — INSERT OR REPLACE for SQLite