Penjelasan terperinci tentang penambahan MySQL, pemadaman, pengubahsuaian dan perangkap biasa

Artikel ini membawakan anda pengetahuan yang berkaitan tentang mysql, yang terutamanya memperkenalkan kandungan yang berkaitan tentang menambah, memadam, mengubah suai dan menyemak serta masalah biasa Mari kita lihat bersama-sama kepada semua orang.

Pembelajaran yang disyorkan: tutorial video mysql

1 🎜>Tambahan, pemadaman, pengubahsuaian dan pertanyaan yang paling biasa digunakan dalam MySQL sepadan dengan pernyataan SQL seperti sisipkan, padam, kemas kini dan pilih Pernyataan ini untuk data pengendalian juga dipanggil Pernyataan Manipulasi Data.

Terdapat 15 jenis kesemuanya iaitu CALL, DELETE, DO, HANDLER, IMPORT TABLE, INSERT, LOAD DATA, LOAD XML, REPL ACE, SELECT, Subqueries, TABLE, UPDATE, VALUES, WITH.

1. sisipkan pernyataan

1.1 Prinsip sisipan pernyataan masukkan, berikut ialah pernyataan umum untuk memasukkan baris data Jika kedua-dua senarai dan senarai VALUES kosong, INSERT mencipta baris dan setiap lajur ditetapkan kepada nilai lalainya > juga boleh digunakan Pernyataan sintaks VALUES ROW() juga boleh memasukkan berbilang baris. Dalam kes ini, setiap senarai nilai mesti disertakan dalam ROW() (pembina baris), seperti yang ditunjukkan di bawah:

Kami sering menggunakan kunci utama semasa membina jadual Apabila sistem kami melakukan penurunan pangkalan data serentak. untuk mengelakkan konflik kunci utama, ON DUPLICATE KEY UPDATE sering digunakan.

-- 插入语句模板
INSERT INTO tbl_name () VALUES();
-- 插入多行
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9);
INSERT INTO tbl_name (a,b,c) VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9);

Nota

: ON DUPLICATE KEY UPDATE ialah sintaks khusus Mysql dan hanya sah untuk Mysql.

Fungsi:

Apabila melakukan operasi sisipan, jika terdapat rekod sedia ada, lakukan operasi kemas kini. Jika klausa ON DUPLICATE KEY UPDATE digunakan dan kekunci pendua menyebabkan KEMASKINI dilakukan, pernyataan tersebut memerlukan kebenaran KEMASKINI untuk mengemas kini lajur. Untuk lajur yang telah dibaca tetapi tidak diubah suai, anda hanya memerlukan kebenaran SELECT (kerana tiada kemas kini diperlukan, yang mudah difahami).

1.2 Perangkap sisipan MySQL

INSERT INTO test ( id, NAME, age ) VALUES( 1, '张三', 13 ) 
	ON DUPLICATE KEY UPDATE age = 13,

Jika mod ketat (mod SQL ketat) tidak didayakan, MySQL menggunakan nilai lalai tersirat untuk mana-mana lajur yang tidak mempunyai nilai lalai nilai lalai yang ditakrifkan secara eksplisit. Jika mod ketat didayakan, ralat akan berlaku jika mana-mana lajur tidak mempunyai nilai lalai. (Mod ketat akan dibincangkan dalam artikel seterusnya).

2. Padam kenyataan

2.1 Prinsip padam kenyataan

padam, sebagai nama mencadangkan, bermakna pemadaman kenyataan memadamkan baris daripada tbl_name dan mengembalikan bilangan baris yang dipadam. Untuk menyemak bilangan baris yang dipadamkan, kami biasanya menggunakan int type return semasa menulis kod:

Jika pengubahsuai LOW_PRIORITY ditentukan, pelayan akan menangguhkan pemadaman sehingga tiada pelanggan lain membaca daripada jadual. Sama ada QUICK menggabungkan indeks untuk operasi pemadaman boleh mengakibatkan pembaziran ruang yang tidak dituntut dalam indeks. ABAIKAN, MySQL mengabaikan ralat yang boleh diabaikan semasa pemadaman baris.

Syarat dalam WHERE menentukan baris mana yang hendak dipadamkan Jika tiada klausa WHERE, semua baris dipadamkan Jika klausa ORDER BY ditentukan, baris dipadamkan dalam susunan yang ditentukan baris yang boleh dipadamkan. Nombornya terhad

-- 删除语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

-- WHERE 中的条件确定要删除哪些行，如果没有WHERE 子句则删除所有行
-- 如果指定了ORDER BY子句，则按指定的顺序删除行
-- LIMIT子句对可以删除的行数进行了限制

-- 如果指定LOW_PRIORITY修饰符，服务器会延迟删除，DELETE直到没有其他客户端从表中读取
-- QUICK是否合并索引进行删除操作，可能会导致索引中未回收的空间浪费
-- IGNORE，MySQL在删除行的过程中忽略可忽略的错误

2.2 Perangkap pemadaman MySQL

1 🎜>Jika Untuk memadamkan banyak baris daripada jadual besar, saiz jadual kunci jadual InnoDB mungkin melebihi. Untuk mengelakkan masalah ini, atau hanya untuk meminimumkan masa jadual kekal terkunci, strategi berikut mungkin membantu:

1. Gunakan prosedur tersimpan untuk pemadaman kumpulan kecil, jangka panjang yang tidak menjejaskan perniagaan Selepas itu pemadaman selesai Ambil prosedur tersimpan di luar talian daripada persekitaran pengeluaran. 2. Pilih baris yang tidak akan dipadamkan dan selaraskan jadual kosong dengan struktur yang sama seperti jadual asal: INSERT INTO t_copy SELECT * FROM t WHERE ... ;

3 RENAMETABLE with atoms Kaedah untuk mengalihkan jadual asal dan menamakan semula salinan kepada nama asal: RENAME TABLE t TO t_old, t_copy TO t;

2 Pemadaman berbilang jadual

1. Anda boleh menentukan berbilang jadual dalam pernyataan DELETE untuk memadamkan baris daripada satu atau lebih jadual berdasarkan syarat dalam klausa WHERE, tetapi anda tidak boleh menggunakan ORDER BY atau LIMIT dalam DELETE berbilang jadual.

3. Prinsip penyataan kemas kini

KEMASKINI ialah penyataan yang mengubah suai baris dalam jadual dan mengembalikan bilangan sebenar baris yang diubah bilangan baris yang dipadam, kami biasanya menulis Kod menggunakan pengembalian jenis int Untuk sintaks jadual tunggal, pernyataan KEMASKINI mengemas kini lajur baris sedia ada dalam jadual bernama dengan nilai baharu.

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
    WHERE t1.id=t2.id AND t2.id=t3.id;

SET Lajur yang akan diubah suai dan nilai yang perlu diberikan, setiap nilai boleh diberikan sebagai ungkapan atau kata kunci DEFAULT untuk menetapkan lajur secara eksplisit kepada nilai lalainya. WHERE menentukan syarat yang mengenal pasti baris mana yang akan dikemas kini. Tanpa klausa WHERE, semua baris akan dikemas kini. Jika klausa ORDER BY ditentukan, baris akan dikemas kini dalam susunan yang ditentukan. Klausa LIMIT mengehadkan bilangan baris yang boleh dikemas kini.

-- 更新单表语法
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

-- 使用LOW_PRIORITY修饰符，UPDATE延迟执行，直到没有其他客户端从表中读取
-- 使用IGNORE修饰符，即使更新期间发生错误，更新语句也不会中止

UPDATE item_id, discounted SET items_info WHERE id = "";

4、select

SELECT用于检索从一个或多个表中选择的行，并且可以包括UNION操作和子查询。从MySQL 8.0.31开始，还支持INTERSECT和EXCEPT操作。后面笔者会单独拿出一篇文章讲解子查询、左连接、查询优化、查询原理等等。

后面更新后会附上连接

二、15种MySQL数据操作语句

类似于增删改查的语句我们在第一节已经学习，本小节主要讲解 CALL、DO、HANDLER、IMPORT TABLE、LOAD DATA、LOAD XML、REPL ACE、Subqueries、TABLE、VALUES、WITH，这11个语句的使用，后续会详细的进行详细分析，关注本专栏。

1、REPLACE语句

REPLACE的工作方式与INSERT完全相同，只是如果表中的一个旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的值，则在插入新行之前会删除旧行。在MySQL 8.0中已不支持DELAYED。

2、CALL语句

CALL语句调用先前使用CREATE procedure定义的存储过程。当过程返回时，客户端程序还可以获得例程内执行的最终语句所影响的行数。

3、TABLE语句

TABLE是MySQL 8.0.19中引入的DML语句，返回命名表的行和列。

4、WITH语句

WITH每个子子句提供一个子查询，该子查询生成一个结果集，并将名称与子查询相关联。

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;

三、MySQL查询陷阱

两个值进行查询，运算或者比较，首先要求数据类型必须一致。如果发现两个数据类型不一致时就会发生隐式类型转换。

问题描述：

分享一个笔者同事曾经发生的产线问题：在一次MySQL查询中，某字段为 varchar 字符串类型，传入参数值为 long 数字类型，发现查询的结果和预期的不一致。

select * from 表 where odr_id = "";
select * from 表 where odr_id = long;

但是由于测试环境的数据量较少，并没有发现，只到上了生产环境，在进行大数据查询时，由于数据库的odr_id是 varchar 类型，查询条件是 long类型，所有每条查询出来的数据都会进行隐式类型转换的比较，直接导致long sql，处理办法是紧急版本上线。

隐式类型转换原理：

如果一个或两个参数均为NULL，则比较的结果为NULL，除了  相等比较运算符。对于NULL NULL，结果为true；如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较；如果两个参数都是整数，则将它们作为整数进行比较。

如果不与数字比较，则将十六进制值视为二进制字符串；如果参数之一是  timestamp 或 datatime column，而另一个参数是常量，则在执行比较之前，该常量将转换为时间戳；如果参数之一是十进制值，则比较取决于另一个参数。

如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较（这里如果生产环境是varchar后果将是灾难级的）；

如果另一个参数是浮点值，则将参数作为浮点值进行比较。；在所有其他情况下，将参数作为浮点数（实数）进行比较。例如，将字符串和数字操作数进行比较，将其作为浮点数的比较。

通过隐式类型转换可以得出上述示例的结果：当查询中有数字时那么会将字符串转化成数字进行比较。所以当你的列为字符串时那么需要将列中字符串进行类型格式转换而进行字符格式转换之后则与索引不一致；当你的列为数字时查询等式为字符串时只是把查询的常量转成数字并不影响列的类型所以依然可以使用索引并没有破坏索引的类型。

推荐学习：mysql视频教程

Atas ialah kandungan terperinci Penjelasan terperinci tentang penambahan MySQL, pemadaman, pengubahsuaian dan perangkap biasa. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!