Inserting Data - Learning MySQL and MariaDB - page 130

It may not be apparent, but everything was replaced. This row has a new value in the
family_id column. If you look earlier in this chapter at the row for this family, you'll
see that the family_id was 330. Because it was the last row in the table, when a new
row was created for its replacement, 331 was assigned to it. The brief_description
has the new value; it said before only Indigobirds .
The REPLACE statement is useful for replacing all of the data for a duplicate row and in-
serting new rows of data for data that isn't already in a given table. It has the potential
problem of replacing all of the columns when you might want to replace only some of
them. Also, in the previous examples, if the scientific_name column was not
UNIQUE or otherwise a key column, new rows would be created for the three families we
tried toreplace with the REPLACE statement.
Priorities When Inserting Data
On a busy MySQL or MariaDB server,there will be times when many people will access
the server at the same time. There will be times when SQL statements are entered simul-
taneously from different sources, perhaps many at the same instant. The server must de-
cide which statements to process first.
Statements that change data ( INSERT , UPDATE , and DELETE )take priority over read
statements ( SELECT statements). Someone who is adding data to the server seems to be
more important than someone reading data. One concern is that the one inserting data
might lose the connection and lose its opportunity. The user retrieving data, in contrast,
can generally wait. For example, on a website that uses MySQL to store purchases, a cus-
tomer entering an order will take priority over another customer who is just browsing
through the list of products.
When the server is executing an INSERT statement for a client, it locks the related tables
for exclusive access and forces other clients to wait until it's finished. This isn't the case
with InnoDB: it locks the rows, rather than the entire table. On a busy MySQL server that
has many simultaneous requests for data, locking a table could cause users to experience
delays, especially when someone is entering many rows of data by using the multiple-row
syntax of the INSERT statement.
Rather than accept the default priorities in MySQL, you can instead set the priority for an
INSERT . You can decide which SQL statements need to be entered as soon as possible
and which can wait. To specify you preferences, the INSERT statement offers priority op-
tions. Enter them between the INSERT keyword and the INTO keyword. There are three
of them: LOW_PRIORITY , DELAYED , and HIGH_PRIORITY . Let's look at each of
them.