ホームページ > 記事 > データベース > MySQL を学ぶために知っておくべき 28 のヒント

MySQL を学ぶために知っておくべき 28 のヒント

Java后端技术全栈転載: 2023-08-16 17:14:561551ブラウズ

情報技術の継続的な発展とインターネット業界の急速な成長に伴い、オープンソースデータベースとして MySQL が広く使用および開発されてきました。現在、MySQL はリレーショナルデータベースの分野において非常に重要な役割を果たしています。

運用と保守、開発、テスト、またはアーキテクトのいずれであっても、データベーステクノロジは 必須の昇給アーティファクトです。データベースの学習、MySQL の学習、具体的に何を学びたいのですか?

MySQL をすぐにマスターするにはどうすればよいですか?

関心を高める

興味彼は最高の先生で、どんな知識を学んでいても、彼の興味があれば学習効率が大幅に向上します。 MySQL5.7 を学習しているか MySQL8.0 を学習しているかに関係なく、例外はありません。

SQL の基礎を強化する

コンピューター分野のテクノロジーは基礎を重視しますが、学習を始めたばかりのときはこのことに気づかないかもしれません。テクノロジーの応用が深化するにつれ、確かな基礎スキルを備えた人だけがテクノロジーの道をより速く、より遠くへ進むことができます。 MySQL を学習する場合、SQL ステートメント は最も基本的な部分であり、多くの操作は SQL ステートメントを通じて実装されます。したがって、学習の過程で、読者は違いを深く理解するために、より多くの SQL ステートメントを作成し、同じ関数を完成させるためにさまざまな実装ステートメントを使用する必要があります。

新しい知識をタイムリーに学ぶ

検索エンジンを正しく効果的に使用すると、MySQL に関する多くの関連知識を検索できます。同時に、問題を解決するために他の人のアイデアを参照したり、他の人の経験から学んだり、最新の技術情報をタイムリーに入手したりできます。

複数の実践的な操作

データベースシステムは非常に操作性が高く、多くの実践的なコンピューター操作が必要です。実際の運用の過程で初めて問題点を発見し、それを解決するための方法やアイデアを考えることで、初めて実際の運用能力が向上します。

MySQL を学習するために知っておくべき 28 のヒントを共有しましょう!

#1. MySQL で特殊文字を使用するにはどうすればよいですか?

一重引用符 '、二重引用符 "、バックスラッシュ \ などの記号は使用できません。

例:

レコード行を Lucifer テーブルに保存する必要があると仮定します。 lucifer's Dog の値、一重引用符 ' は、エスケープしないと正常に実行できません:

mysql> create table lucifer (id int,name char(100));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into lucifer values (1,&#39;lucifer&#39;s dog&#39;);
    &#39;> 
    &#39;> mysql> 

^C
mysql>

MySQL では、これらの特殊文字をエスケープ文字と呼び、入力時にバックスラッシュ記号 \ で始める必要があるため、一重引用符と二重引用符を使用する場合はそれぞれ を入力する必要があります。 \' または \"。バックスラッシュを入力する場合は、\\ と入力する必要があります。その他の特殊文字には、キャリッジリターン \r やラインフィードなどがあります。 \n、タブ文字 \tab、バックスペース文字 \b など

mysql> create table lucifer (id int,name char(100));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into lucifer values (1,&#39;lucifer\&#39;s dog&#39;);
Query OK, 1 row affected (0.00 sec)

mysql> select * from lucifer;
+------+---------------+
| id   | name          |
+------+---------------+
|    1 | lucifer&#39;s dog |
+------+---------------+
1 row in set (0.00 sec)
mysql>

? 注: はこれらの特殊文字をデータベースに挿入する場合は、エスケープする必要があります。

2. ファイルは MySQL に保存できますか?

答えはもちろんイエスです!

MySQL の BLOB および TEXT フィールドタイプは、大量のデータファイルを保存できます。これらのデータ型を使用して、画像、音声、または Web ページやドキュメントなどの大容量のテキストコンテンツを保存できます。

mysql> create table view(id int unsigned NOT NULL AUTO_INCREMENT, catid int,title varchar(256),picture MEDIUMBLOB, content TEXT,PRIMARY KEY (id));
Query OK, 0 rows affected (0.03 sec)

mysql> show fields from view;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int unsigned | NO   | PRI | NULL    | auto_increment |
| catid   | int          | YES  |     | NULL    |                |
| title   | varchar(256) | YES  |     | NULL    |                |
| picture | mediumblob   | YES  |     | NULL    |                |
| content | text         | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql>

BLOB または TEXT を使用して大容量データを保存できますが、これらの処理はフィールドはデータベースのパフォーマンスを低下させます。

? 注意： 如果并非必要，可以选择只储存文件的路径。

3、MySQL 中如何执行区分大小写的字符串比较？

MySQL 是 不区分大小写 的，因此字符串比较函数也不区分大小写。

mysql> select &#39;TRUE&#39; from dual where &#39;DOG&#39; = &#39;dog&#39;;
+------+
| TRUE |
+------+
| TRUE |
+------+
1 row in set (0.00 sec)

如果想执行区分大小写的比较，可以在字符串前面添加 BINARY 关键字。

mysql> select &#39;TRUE&#39; from dual where BINARY&#39;DOG&#39; = &#39;dog&#39;;
Empty set (0.00 sec)

mysql>

例如默认情况下，’DOG‘=’dog‘ 返回结果为 TRUE，如果使用 BINARY 关键字，BINARY’DOG’=‘dog’ 结果为 FALSE，在区分大小写的情况下，’DOG’ 与 ’dog’ 并不相同。

4、如何从日期时间值中获取年、月、日等部分日期或时间值？

MySQL 中，日期时间值以字符串形式存储在数据表中，因此可以使用字符串函数分别截取日期时间值的不同部分。

mysql> create table lucifer(date date);
Query OK, 0 rows affected (0.04 sec)

mysql> show fields from lucifer;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| date  | date | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into lucifer values (now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from lucifer;
+------------+
| date       |
+------------+
| 2021-11-25 |
+------------+
1 row in set (0.00 sec)

例如某个名称为 date 的字段有值 2021-11-25，如果只需要获得年值，可以输入 LEFT(date, 4)，这样就获得了字符串左边开始长度为 4 的子字符串，即 YEAR 部分的值；

mysql> select LEFT(date, 4) from lucifer;
+---------------+
| LEFT(date, 4) |
+---------------+
| 2021          |
+---------------+
1 row in set (0.00 sec)

如果要获取月份值，可以输入 MID(date,6,2)，字符串第 6 个字符开始，长度为 2 的子字符串正好为 date 中的月份值。同理，读者可以根据其他日期和时间的位置，计算并获取相应的值。

mysql> select MID(date,6,2) from lucifer;
+---------------+
| MID(date,6,2) |
+---------------+
| 11            |
+---------------+
1 row in set (0.00 sec)

5、如何改变默认的字符集？

CONVERT() 函数改变指定字符串的默认字符集！

MySQL 的安装和配置过程中，其中的一个步骤是可以选择 MySQL 的默认字符集。但是，如果只改变字符集，没有必要把配置过程重新执行一遍，在这里，一个简单的方式是 修改配置文件。

读者可以在修改字符集时使用 SHOW VARIABLES LIKE 'character_set_%'; 或者 status 命令查看当前字符集，以进行对比。

mysql> SHOW VARIABLES LIKE &#39;character_set_%&#39;;
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8mb3                    |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> status
--------------
mysql  Ver 8.0.26-0ubuntu0.21.04.3 for Linux on aarch64 ((Ubuntu))

Connection id:          10
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          &#39;&#39;
Using delimiter:        ;
Server version:         8.0.26-0ubuntu0.21.04.3 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 36 min 55 sec

Threads: 2  Questions: 325  Slow queries: 0  Opens: 181  Flush tables: 3  Open tables: 69  Queries per second avg: 0.146
--------------

mysql>

MySQL 配置文件名称为 my.cnf，该文件在 MySQL 的安装目录下面。修改配置文件中的 default-character-set 和 character-set-server 参数值，将其改为想要的字符集名称，如 gbk、gb2312、latinl 等，修改完之后重新启动 MySQL 服务，即可生效。

## 找到 my.cnf 位置
root@modb:~# find /etc -iname my.cnf -print
/etc/alternatives/my.cnf
/etc/mysql/my.cnf

## 修改字符集
在[client ]下面加入
default-character-set=utf8
在[ mysqld ] 下面加
character_set_server=utf8

## 重启 mysql 生效
service mysql restart

此时，登录 MySQL 后使用 SHOW VARIABLES LIKE 'character_set_%'; 或者 status 命令查看修改结果！

6、DISTINCT 可以应用于所有的列吗？

查询结果中，如果需要对列进行降序排序，可以使用 DESC，这个关键字只能对其前面的列进行降序排列。

mysql> select * from lucifer;
+------+----------+
| id   | name     |
+------+----------+
|    1 | lucifer  |
|    2 | lucifer1 |
|    3 | lucifer2 |
+------+----------+
3 rows in set (0.00 sec)

mysql> select * from lucifer order by id desc;
+------+----------+
| id   | name     |
+------+----------+
|    3 | lucifer2 |
|    2 | lucifer1 |
|    1 | lucifer  |
+------+----------+
3 rows in set (0.00 sec)

例如，要对多列都进行降序排序，必须要在每一列的列名后面加 DESC 关键字。

mysql> select * from lucifer order by id desc,name desc;
+------+----------+
| id   | name     |
+------+----------+
|    3 | lucifer2 |
|    2 | lucifer1 |
|    1 | lucifer  |
+------+----------+
3 rows in set (0.00 sec)

而 DISTINCT 不同，DISTINCT 不能部分使用。换句话说，DISTINCT 关键字应用于所有列而不仅是它后面的第一个指定列。

例如，查询 2 个字段 sex，age，如果不同记录的这 2 个字段的组合值都不同，则所有记录都会被查询出来。

mysql> select * from lucifer;
+------+-----------+--------+------+
| id   | name      | sex    | age  |
+------+-----------+--------+------+
|    1 | xiaoli    | male   |   20 |
|    1 | xiaoliu   | female |   21 |
|    1 | xiaozhang | female |   21 |
|    1 | xiaowu    | female |   21 |
+------+-----------+--------+------+
4 rows in set (0.00 sec)

mysql> select distinct sex,age from lucifer;
+--------+------+
| sex    | age  |
+--------+------+
| male   |   20 |
| female |   21 |
+--------+------+
2 rows in set (0.00 sec)

mysql>

7、ORDER BY 可以和 LIMIT 混合使用吗？

在使用 ORDER BY 子句时，应保证其位于 FROM 子句之后，如果使用 LIMIT，则必须位于 ORDER BY 之后，如果子句顺序不正确，MySQL 将产生错误消息。

✅ 正确用法：

mysql> select * from lucifer order by age desc limit 2,4;
+------+--------+--------+------+
| id   | name   | sex    | age  |
+------+--------+--------+------+
|    1 | xiaowu | female |   21 |
|    1 | xiaoli | male   |   20 |
+------+--------+--------+------+
2 rows in set (0.00 sec)

❎ 错误用法：

mysql> select * from lucifer limit 2,4 order by age desc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;order by age desc&#39; at line 1
mysql>

8、什么时候使用引号？

在查询的时候，会看到在 WHERE 子句中使用条件，有的值加上了单引号，而有的值未加。

mysql> select * from lucifer where sex = &#39;female&#39;;
+------+-----------+--------+------+
| id   | name      | sex    | age  |
+------+-----------+--------+------+
|    1 | xiaoliu   | female |   21 |
|    1 | xiaozhang | female |   21 |
|    1 | xiaowu    | female |   21 |
+------+-----------+--------+------+
3 rows in set (0.00 sec)

mysql>

单引号用来限定字符串，如果将值与字符串类型列进行比较，则需要限定引号；而用来与数值进行比较则不需要用引号。

mysql> select * from lucifer where age = 20;
+------+--------+------+------+
| id   | name   | sex  | age  |
+------+--------+------+------+
|    1 | xiaoli | male |   20 |
+------+--------+------+------+
1 row in set (0.00 sec)

mysql>

9、在 WHERE子句中 AND 和 OR 必须使用圆括号吗？

任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确操作顺序。

mysql> select * from lucifer where (age = 20 or sex = &#39;female&#39;) and name != &#39;xiaowu&#39;;
+------+-----------+--------+------+
| id   | name      | sex    | age  |
+------+-----------+--------+------+
|    1 | xiaoli    | male   |   20 |
|    1 | xiaoliu   | female |   21 |
|    1 | xiaozhang | female |   21 |
+------+-----------+--------+------+
mysql> 3 rows in set (0.00 sec)

如果条件较多，即使能确定计算次序，默认的计算次序也可能会使 SQL 语句不易理解，因此使用括号明确操作符的次序，是一个好的习惯。

10、更新或者删除表时必须指定 WHERE子句吗？

个人建议所有的 UPDATE 和 DELETE 语句全都在 WHERE 子句中指定条件。

mysql> update lucifer set age = 22 where name = &#39;xiaoliu&#39;;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from lucifer where name = &#39;xiaoliu&#39;;
+------+---------+--------+------+
| id   | name    | sex    | age  |
+------+---------+--------+------+
|    1 | xiaoliu | female |   22 |
+------+---------+--------+------+
1 row in set (0.00 sec)

mysql>

如果省略 WHERE 子句，则 UPDATE 或 DELETE 将被应用到表中所有的行。

mysql> update lucifer set age = 22;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 4  Changed: 3  Warnings: 0

mysql> select * from lucifer;
+------+-----------+--------+------+
| id   | name      | sex    | age  |
+------+-----------+--------+------+
|    1 | xiaoli    | male   |   22 |
|    1 | xiaoliu   | female |   22 |
|    1 | xiaozhang | female |   22 |
|    1 | xiaowu    | female |   22 |
+------+-----------+--------+------+
4 rows in set (0.00 sec)

mysql>

因此，除非确实打算更新或者删除所有记录，否则要注意使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。

? 注: 取り返しのつかない結果を避けるために、テーブルを更新および削除する前に、SELECT ステートメントを使用して削除する必要があるレコードを確認することをお勧めします。

#11. インデックスはデータベースのパフォーマンスにとって非常に重要ですが、どのように使用すればよいでしょうか?

インデックスの利点:

一意のインデックスを作成することで、それぞれの一意性を確保できます。データベーステーブル内のデータ行の一意性。
すべての MySQL 列タイプにインデックスを設定できます。
を使用すると、データクエリを大幅に高速化できます。これがインデックスを使用する主な理由です。
データの参照整合性を実現するという点で、テーブル間の接続を高速化できます。
#データクエリにグループ化句と並べ替え句を使用すると、クエリ内のグループ化と並べ替えの時間も大幅に短縮できます

# # 欠点:

データベースに適切なインデックスを選択するのは複雑な作業です。列が少ないインデックスでは、必要なディスク容量とメンテナンスのオーバーヘッドが少なくなります。大きなテーブルに複数の結合インデックスが作成されると、インデックスファイルも急速に拡張されます。

一方、インデックスの数が増えると、より多くのクエリをカバーできます。最も効果的なインデックスを見つけるには、いくつかの異なる設計を試してみる必要がある場合があります。データベーススキーマやアプリケーション設計に影響を与えることなく、インデックスを追加、変更、削除できます。

因此，应尝试多个不同的索引从而建立最优的索引。

12、尽量使用短索引（前缀索引）

对字符串类型的字段进行索引，如果可能应该指定一个前缀长度。

例如，如果有一个 CHAR(255) 的列，如果在前 10 个或 30 个字符内，多数值是惟一的，则不需要对整个列进行索引。

mysql> select * from lucifer;
+------+-----------+--------+------+
| id   | name      | sex    | age  |
+------+-----------+--------+------+
|    1 | xiaoli    | male   |   22 |
|    1 | xiaoliu   | female |   22 |
|    1 | xiaozhang | female |   22 |
|    1 | xiaowu    | female |   22 |
+------+-----------+--------+------+
4 rows in set (0.00 sec)

mysql> create index idx_lucifer_name on lucifer (name(4));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from lucifer;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| lucifer |          1 | idx_lucifer_name |            1 | name        | A         |           1 |        4 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

mysql>

短索引不仅可以提高查询速度而且可以节省磁盘空间、减少 I/O 操作。

13、MySQL 存储过程和函数有什么区别？

在本质上它们都是存储程序。

函数：

只能通过 return 语句返回单个值或者表对象；
限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用等等；
可以嵌入在 SQL 语句中使用，可以在 SELECT 语句中作为查询语句的一个部分调用；

存储过程：

#Return は許可されていませんが、out パラメータを通じて複数の値を返すことができます。
制限は比較的小さいです。
通常、独立した部分として実行されます;

14. ストアドプロシージャの内容は変更できますか?

それはできません!

現在、MySQL では既存のストアドプロシージャコードを変更できません。ストアドプロシージャを変更する必要がある場合は、DROP ステートメントを使用して削除し、コードを書き直すか、新しいストレージプロセスを作成する必要があります。。

この点では、Oracle の方が優れていると言わざるを得ません。

#15. ストアドプロシージャ内で他のストアドプロシージャを呼び出すことはできますか? ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃できる！ストアドプロシージャには、ユーザー定義の一連の SQL ステートメントが含まれています。CALL ステートメントを使用して、ストアドプロシージャを呼び出すことができます。もちろん、CALL ステートメントを使用して、他のストアドプロシージャを呼び出すこともできます。ただし、DROP ステートメントを使用して他のストアドプロシージャを削除することはできません。

#16. ストアドプロシージャのパラメータは、データテーブルのフィールド名と同じであってはなりません。

ストアドプロシージャパラメータリストを定義するときは、パラメータ名とデータベーステーブル内のフィールド名を区別することに注意する必要があります。区別しないと、予期しない結果が発生します。

17. ストアドプロシージャのパラメータを中国語にできますか?

通常の状況では、中国語のパラメータがストアドプロシージャに渡される場合があります。たとえば、ストアドプロシージャはユーザー名に基づいてユーザーの情報を検索します。 . パラメータ値は中国語である場合があります。このとき、ストアドプロシージャを定義するときに最後に文字セット gbk を追加する必要があります。そうしないと、中国語パラメータを使用してストアドプロシージャを呼び出すときにエラーが発生します。たとえば、userInfo ストアドプロシージャを定義する場合、コードは次のとおりです。

##CREATE PROCEDURE useInfo(IN u_name VARCHAR (50)character set gbk, OUT u_age INT)

#18. ビューとビューの違いと関係は何ですか? MySQLのテーブル?

2 つの違い:

ビューはコンパイルされた SQL ステートメントであり、SQL に基づいています。ステートメントの結果セットはテーブルに視覚化されますが、テーブルは視覚化されません。
#ビューには実際の物理レコードがありませんが、ベーステーブルには物理レコードがあります。
テーブルはコンテンツであり、ビューはウィンドウです。
テーブルは物理スペースを占有しますが、ビューは物理スペースを占有しません。ビューは単なるものです。論理概念。テーブルは時間内に変更できますが、ビューのみ使用できます。変更するステートメントを作成します。
ビューは、データテーブルを表示する方法です。クエリを実行できます。データテーブル内の特定のフィールドで構成されるデータ。これは、いくつかの SQL ステートメントのコレクションにすぎません。セキュリティの観点から、ビューはユーザーがデータテーブルにアクセスできないようにすることができるため、ユーザーはテーブルの構造を知りません。
テーブルはグローバルスキーマ内のテーブルに属しており、実際のテーブルです。 ; ビューはローカルスキーマに属しますテーブルは仮想テーブルです;
ビューの作成と削除はビュー自体にのみ影響し、対応する基本テーブルには影響しません;

# #両者の関係:

ビュー (ビュー) は、基本テーブルに基づいて構築されたテーブルであり、その構造 (つまり、定義された列) と内容(つまり、すべてのレコード) は、基本テーブルの存在に基づいて存在する基本テーブルから取得されます。

一个视图可以对应一个基本表，也可以对应多个基本表。

视图是基本表的抽象和在逻辑意义上建立的新关系。

19、使用触发器时须特别注意！

在使用触发器的时候需要注意，对于相同的表，相同的事件只能创建一个触发器。

mysql> create trigger lucifer_tri before insert on lucifer for each row set NEW.id=NEW.id+1;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> select * from lucifer;
+------+-----------+--------+------+
| id   | name      | sex    | age  |
+------+-----------+--------+------+
|    1 | xiaoli    | male   |   22 |
|    1 | xiaoliu   | female |   22 |
|    1 | xiaozhang | female |   22 |
|    1 | xiaowu    | female |   22 |
|    1 | lucifer   | male   |   20 |
|    1 | lucifer   | male   |   20 |
+------+-----------+--------+------+
6 rows in set (0.00 sec)

mysql> insert into lucifer values(1,&#39;lucifer&#39;,&#39;male&#39;,20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from lucifer;
+------+-----------+--------+------+
| id   | name      | sex    | age  |
+------+-----------+--------+------+
|    1 | xiaoli    | male   |   22 |
|    1 | xiaoliu   | female |   22 |
|    1 | xiaozhang | female |   22 |
|    1 | xiaowu    | female |   22 |
|    1 | lucifer   | male   |   20 |
|    1 | lucifer   | male   |   20 |
|    2 | lucifer   | male   |   20 |
+------+-----------+--------+------+
7 rows in set (0.00 sec)

比如对表 lucifer 创建了一个 BEFORE INSERT 触发器，那么如果对表 lucifer 再次创建一个 BEFORE INSERT 触发器，MySQL 将会报错，此时，只可以在表 lucifer 上创建 AFTER INSERT 或者 BEFORE UPDATE 类型的触发器。

mysql> create trigger lucifer_tri before insert on lucifer for each row set NEW.id=NEW.id+1;
ERROR 1359 (HY000): Trigger already exists
mysql>

灵活的运用触发器将为操作省去很多麻烦。

20、及时删除不再需要的触发器

触发器定义之后，每次执行触发事件，都会激活触发器并执行触发器中的语句。

如果需求发生变化，而触发器没有进行相应的改变或者删除，则触发器仍然会执行旧的语句，从而会影响新的数据的完整性。

mysql> drop trigger lucifer_tri;
Query OK, 0 rows affected (0.03 sec)

mysql>

因此，要将不再使用的触发器及时删除。

21、应该使用哪种方法创建用户？（3种方式）

创建用户有 3 种方法：

使用 CREATE USER 语句创建用户
在 mysql.user 表中添加用户
使用 GRANT 语句创建用户（仅限 MySQL 8 版本以下使用）

一般情况，最好使用 GRANT 或者 CREATE USER 语句，而不要直接将用户信息插入 user 表，因为 user 表中存储了全局级别的权限以及其他的账户信息，如果意外破坏了 user 表中的记录，则可能会对 MySQL 服务器造成很大影响。

-- 使用 CREATE USER 语句创建用户
mysql> create user &#39;lucifer&#39;@&#39;localhost&#39; identified by &#39;lucifer&#39;;
Query OK, 0 rows affected (0.01 sec)

mysql> 

-- 在 mysql.user 表中添加用户
mysql> select MD5(&#39;lucifer&#39;);
+----------------------------------+
| MD5(&#39;lucifer&#39;)                   |
+----------------------------------+
| cae33a0264ead2ddfbc3ea113da66790 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, ssuex509_i09_sr, x5ubject) VALUES (&#39;lohoscalt&#39;,uci &#39;lfer MD5(&#39;1&#39;,lucifer&#39;), &#39;&#39;, &#39;&#39;,; &#39;&#39;)
Query OK, 1 row affected (0.01 sec)

mysql> 

-- 使用 GRANT 语句创建用户
mysql> GRANT SELECT ON*.* TO &#39;lucifer2&#39;@localhost IDENTIFIED BY &#39;lucifer&#39;;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;IDENTIFIED BY &#39;lucifer&#39;&#39; at line 1
mysql>

? 注意： 由于测试使用的是 MySQL 8 版本，已经不支持 GRANT 直接创建用户，5.7 版本依然是支持的。

22、mysqldump 备份的文件只能在 MySQL 中使用吗？

逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备。

mysqldump 备份的文本文件实际是数据库的一个副本，使用该文件不仅可以在 MySQL 中恢复数据库，而且通过对该文件的简单修改，可以使用该文件在 SQL Server 或者 Sybase 等其他数据库中恢复数据库。

root@modb:~# mysqldump -uroot -p hr > /root/hr.db
Enter password: 
root@modb:~# 
root@modb:~# ll hr.db 
-rw-r--r-- 1 root root 25327 Nov 26 08:52 hr.db

这在某种程度上实现了数据库之间的迁移。

23. バックアップツールを選択するにはどうすればよいですか?

バックアップ方法 (データベースをオフラインにする必要があるかどうか) に基づいて、バックアップは次のように分類できます。

ホットバックアップ
コールドバックアップ
ウォームバックアップ

MySQL で実行さまざまなバックアップ方法も考慮する必要がありますストレージエンジンがサポートしているかどうか。たとえば、MyISAM はホットバックアップをサポートしていませんが、ウォームバックアップとコールドバックアップをサポートしています。 InnoDB は、ホットスタンバイ、ウォームスタンバイ、コールドスタンバイをサポートしています。

通常、バックアップが必要なデータは次のカテゴリに分類されます:

テーブルデータ
バイナリログ、InnoDB トランザクションログ
#コード (ストアドプロシージャ、ストアド関数、トリガー、イベントスケジューラ)
#一般的に使用されるいくつかのバックアップツールを次に示します:

mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备。
cp、tar 等归档复制工具：物理备份工具，适用于所有的存储引擎、冷备、完全备份、部分备份。
lvm2 snapshot：借助文件系统管理工具进行备份。
mysqlhotcopy：名不副实的一个工具，仅支持 MyISAM 存储引擎。
xtrabackup：一款由 percona 提供的非常强大的 InnoDB/XtraDB 热备工具，支持完全备份、增量备份。

直接复制数据文件是最为直接、快速的备份方法，但缺点是基本上不能实现增量备份。备份时必须确保没有使用这些表。如果在复制一个表的同时服务器正在修改它，则复制无效。备份文件时，最好关闭服务器，然后重新启动服务器。

24、平时应该打开哪些日志？

日志既会影响 MySQL 的性能，又会占用大量磁盘空间。因此，如果不必要，应尽可能少地开启日志。

根据不同的使用环境，可以考虑开启不同的日志。

例如，在开发环境中优化查询效率低的语句，可以开启慢查询日志；

开启慢查询日志： 可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

-- 检查是否开启慢查询
mysql> show variables like &#39;slow_query%&#39;;
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | OFF                          |
| slow_query_log_file | /var/lib/mysql/modb-slow.log |
+---------------------+------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like &#39;long_query_time&#39;;
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

-- 开启慢查询日志
mysql> set global slow_query_log=&#39;ON&#39;; 
Query OK, 0 rows affected (0.00 sec)

-- 设置查询超过10秒就记录
mysql> set global long_query_time=10;
Query OK, 0 rows affected (0.00 sec)

-- 再次检查是否开启
mysql> show variables like &#39;slow_query%&#39;;
mysql> +---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | ON                           |
| slow_query_log_file | /var/lib/mysql/modb-slow.log |
+---------------------+------------------------------+
2 rows in set (0.00 sec)

如果需要记录用户的所有查询操作，可以开启通用查询日志；

mysql> show variables like &#39;general_log%&#39;;
+------------------+-------------------------+
| Variable_name    | Value                   |
+------------------+-------------------------+
| general_log      | OFF                     |
| general_log_file | /var/lib/mysql/modb.log |
+------------------+-------------------------+
2 rows in set (0.00 sec)

-- 开启通用查询日志
mysql> SET GLOBAL general_log=1; 
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like &#39;general_log%&#39;;
+------------------+-------------------------+
| Variable_name    | Value                   |
+------------------+-------------------------+
| general_log      | ON                      |
| general_log_file | /var/lib/mysql/modb.log |
+------------------+-------------------------+
2 rows in set (0.00 sec)

如果需要记录数据的变更，可以开启二进制日志；错误日志是默认开启的。

mysql> show variables like &#39;log_bin%&#39;;
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)

mysql>

25、如何使用二进制日志？

二进制日志主要用来记录数据变更。

如果需要记录数据库的变化，可以开启二进制日志。基于二进制日志的特性，不仅可以用来进行数据恢复，还可用于数据复制。

root@modb:/var/lib/mysql# ls binlog*
binlog.000001  binlog.000002  binlog.index
root@modb:/var/lib/mysql# mysqlbinlog binlog.000001 | mysql -u root -p                                            
Enter password: 
root@modb:/var/lib/mysql#

在数据库定期备份的情况下，如果出现数据丢失，可以先用备份恢复大部分数据，然后使用二进制日志恢复最近备份后变更的数据。在双机热备情况下，可以使用 MySQL 的二进制日志记录数据的变更，然后将变更部分复制到备份服务器上。

26、如何使用慢查询日志？

慢查询日志主要用来记录查询时间较长的日志。

在开发环境下，可以开启慢查询日志来记录查询时间较长的查询语句，然后对这些语句进行优化。

root@modb:/var/lib/mysql# cat /var/lib/mysql/modb-slow.log
/usr/sbin/mysqld, Version: 8.0.26-0ubuntu0.21.04.3 ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
root@modb:/var/lib/mysql#

通过配 long_query_time 的值，可以灵活地掌握不同程度的慢查询语句。

27、是不是索引建立得越多越好？

合理的索引可以提高查询的速度，但不是索引越多越好。

在执行插入语句的时候，MySQL 要为新插入的记录建立索引。所以过多的索引会导致插入操作变慢。原则上是只有查询用的字段才建立索引。

使用索引时，需要综合考虑索引的优点和缺点。

28、如何使用查询缓冲区？

查询缓冲区可以提高查询的速度，但是这种方式只适合查询语句比较多、更新语句比较少的情况。

默认情况下查询缓冲区的大小为 0，也就是不可用。可以修改 queiy_cache_size 以调整查询缓冲区大小；修改 query_cache_type 以调整查询缓冲区的类型。

在 my.cnf 中修改 query_cache_size 和 query_cache_type 的值如下所示：

[mysqld]
query_cache_size= 512M 
query_cache_type= 1
query_cache_type=1

表示开启查询缓冲区。

只有在查询语句中包含 SQL_NO_CACHE 关键字时，才不会使用查询缓冲区。可以使用 FLUSH QUERY CACHE 语句来刷新缓冲区，清理查询缓冲区中的碎片。

以上がMySQL を学ぶために知っておくべき 28 のヒントの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

声明：

この記事はJava后端技术全栈で複製されています。侵害がある場合は、admin@php.cn までご連絡ください。

前の記事：インタビュアー: 数千万のデータをすばやくクエリするにはどうすればよいですか?次の記事：インタビュアー: 数千万のデータをすばやくクエリするにはどうすればよいですか?

続きを見る