MySQL 5.7.21 on Ubuntu
---
按照8020法则，整理常用的MySQL命令、配置。
目录
1、help
2、show命令
3、EXPLAIN | DESCRIBE | DESC
4、数据库
5、用户
6、权限
7、数据表及字段
8、索引
help 或 ?
help contents
help <item>
help show
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE TABLE tbl_name
SHOW DATABASES [like_or_where]
SHOW [FULL] PROCESSLIST
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
mysql> show table status G;
查看存储引擎：来自博客园
mysql> show Storage Engines; -- 来自 help contents
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)三者是同一个命令。
The DESCRIBE and EXPLAIN statements are synonyms（同义词）. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query).用途：来自博客园
1）查看数据表结构（及字段），常用DESC
DESC使用
mysql> show tables;
+-----------------+
| Tables_in_gitee |
+-----------------+
| user |
+-----------------+
1 row in set (0.00 sec)
mysql> desc user;
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| age | int(11) | YES | | 0 | |
| create_time | datetime | YES | | CURRENT_TIMESTAMP | |
| email | varchar(255) | NO | UNI | | |
| name | varchar(100) | NO | | NULL | |
| nick_name | varchar(200) | NO | UNI | NULL | |
| password | varchar(384) | NO | | NULL | |
| password_algorithm | varchar(20) | NO | | NULL | |
| password_salt | varchar(20) | NO | | NULL | |
| phone | varchar(20) | NO | UNI | | |
| sex | int(11) | YES | | NULL | |
| update_time | datetime | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
12 rows in set (0.01 sec)
mysql> desc user age;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| age | int(11) | YES | | 0 | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)2）分析SQL命令，常用EXPLAIN
EXPLAIN分析
mysql> explain insert into user(age,first_name,last_name,name) value(12,'lib','Ma','user090801');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | INSERT | user | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.00 sec)
# 在EXPLAIN后执行INSERT，只是分析，没有添加用户
mysql> select * from user;
+----+------+---------------------+------------+-----------+---------------------+------+------+
| id | age | create_time | first_name | last_name | update_time | name | sex |
+----+------+---------------------+------------+-----------+---------------------+------+------+
| 2 | 0 | 2021-08-21 16:03:14 | Chloe | O'Brian | 2021-08-21 16:03:14 | | NULL |
| 3 | 0 | 2021-08-21 16:03:14 | Kim | Bauer | 2021-08-21 16:03:14 | | NULL |
| 4 | 0 | 2021-08-21 16:03:14 | David | Palmer | 2021-08-21 16:03:14 | | NULL |
| 5 | 0 | 2021-08-21 16:03:14 | Michelle | Dessler | 2021-08-21 16:03:14 | | NULL |
| 6 | 27 | 2021-08-22 09:25:48 | He | Li | 2021-08-22 09:37:44 | | NULL |
+----+------+---------------------+------------+-----------+---------------------+------+------+
5 rows in set (0.00 sec)
mysql>
mysql> insert into user(age,first_name,last_name,name) value(12,'lib','Ma','user090801');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+----+------+---------------------+------------+-----------+---------------------+------------+------+
| id | age | create_time | first_name | last_name | update_time | name | sex |
+----+------+---------------------+------------+-----------+---------------------+------------+------+
| 2 | 0 | 2021-08-21 16:03:14 | Chloe | O'Brian | 2021-08-21 16:03:14 | | NULL |
| 3 | 0 | 2021-08-21 16:03:14 | Kim | Bauer | 2021-08-21 16:03:14 | | NULL |
| 4 | 0 | 2021-08-21 16:03:14 | David | Palmer | 2021-08-21 16:03:14 | | NULL |
| 5 | 0 | 2021-08-21 16:03:14 | Michelle | Dessler | 2021-08-21 16:03:14 | | NULL |
| 6 | 27 | 2021-08-22 09:25:48 | He | Li | 2021-08-22 09:37:44 | | NULL |
| 7 | 12 | 2021-09-08 22:05:04 | lib | Ma | 2021-09-08 22:05:04 | user090801 | NULL |
+----+------+---------------------+------------+-----------+---------------------+------------+------+
6 rows in set (0.00 sec)
# 查看表user的所有
mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | |
| user | 1 | idx_fn | 1 | first_name | A | 6 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
# 使用explain来分析各种select语句
mysql> explain select * from user where first_name = 'lib';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | idx_fn | idx_fn | 102 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where first_name = 'lib' and last_name = 'Ma';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ref | idx_fn | idx_fn | 102 | const | 1 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where first_name = 'lib' and last_name = 'zhao';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ref | idx_fn | idx_fn | 102 | const | 1 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where last_name = 'zhao';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
explain更多信息，可以看 参考文档4 以及其他网友的好文。
创建：来自博客园
早期的UTF-8编码，不支持emoji表情等：
CREATE DATABASE IF NOT EXISTS dbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
真正的UTF-8编码：
CREATE DATABASE IF NOT EXISTS dbname DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
切换（选择）数据库：
USE db_name
显示当前数据库信息：
status
删除数据库：
DROP DATABASE IF EXISTS db_name;
修改数据库字符集：
ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci;
系统用户表：mysql.user
添加：来自博客园
默认Host为 %：'test4'@'%'
CREATE USER test1 IDENTIFIED BY '123';
设置（修改）密码：
1）ALTER USER test1 IDENTIFIED BY '111';
2）SET PASSWORD FOR test1 = '222';
3）SET PASSWORD FOR test1 = PASSWORD('222');
删除：
DROP USER IF EXISTS TEST3,test4;
GRAMT 授权
REVOKE 回收权限
用户对：全局、数据库、表、列、存储过程、代理的权限
查看权限：来自博客园
SHOW GRANTS FOR user@host;
授予所有权限：
第一个* 表示 数据库，第二个*表示数据表（也可能是其它？）
GRANT ALL PRIVILEGES ON *.* TO user@host;
授予查询权限：
GRANT SELECT ON db_example.* TO user@host;
回收所有权限：
REVOKE ALL PRIVILEGES ON *.* FROM user@host;
回收查询权限：
REVOKE SELECT ON db_example.* FROM 'test2'@'localhost';
刷新权限：
REVODE、DROP USER后执行
FLUSH PRIVILEGES;
5种约束：
- 默认 DEFAULT
- 非空 NOT NULL
- 唯一 UNIQUE 也是 唯一性索引
- 主键 PRIMARY KEY 也是 主键索引
- 外键 [CONSTRAINT fk_name] FOREIGN KEY...REFERENCES
自动增长：AUTO_INCREMENT
一个表一个；
定义为间：UNIQUE KEY，PRIMARY KEY；
从1开始；
以上 约束、自动增长 的关键词添加到 字段类型 后面即可生效。来自博客园
数据类型：
数字类型
TINYINT SMALLINT MEDIUMINT INT BIGINT
FLOAT DOUBLE
DECIMAL(M,D)
BIT(M)
时间和日期类型
YEAR DATE TIME
DATETIME 1000~9999年，NOW()函数
TIMESTAMP 时间戳，1970~2038年，CURRENT_TIMESTAMP，默认情况下，自动设置 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
字符串类型
CHAR VARCHAR
TINYTEXT TEXT MEDIUMTEXT LONGTEXT
ENUM
SET
BINARY VARBINARY
BLOB
JSON 5.7.8开始支持
创建数据表：
2个字段：id、name
CREATE TABLE table1(id INT, name VARCHAR(32));
查看数据表的创建语句：来自博客园
里面会包含数据表的 引擎、默认字符集。
SHOW CREATE TABLE table1;
修改数据表字符集：
会影响新增字段的默认字符集。
ALTER TABLE table1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
查看数据表status：
当前数据库下所有表，末尾的 G 表示 结果竖着展示。
show table status G
查看数据表结构：
show full columns from table1;
可以看到字符集、评论等更多信息。
注，没有full时，和desc table1;相同。
mysql> show full columns from table1;
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(11) | NULL | YES | | NULL | | select,insert,update,references | |
| name | varchar(32) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)
查看所有数据表：
show tables; -- 当前数据库下的所有表
show tables from db_name; -- 数据库db_name下的所有表
删除数据表：来自博客园
DROP TABLE IF EXISTS table1;
查看字段信息：
desc table1 name;
修改字段名：
新字段类型是必须的。
ALTER TABLE table1 CHANGE name newname VARCHAR(32);
修改字段类型：
ALTER TABLE table1 MODIFY newname DATETIME;
新增字段：
ALTER TABLE table1 ADD age INT;
ALTER TABLE table1 ADD address VARCHAR(500); -- 字符集沿用数据表的
删除字段：
ALTER TABLE table1 DROP love;
常见的5种索引：来自博客园
- 普通索引 INDEX 或 KEY
- 唯一性索引
- 主键索引 （聚簇cu4索引）
- 全文索引 FULLTEXT INDEX，CHAR、VARCHAR、TEXT类型适用
- 空间索引 SPATIAL INDEX
分类法2：单列索引、复合索引
查询数据表的索引：
show index from table1;
查询表的索引
mysql> show index from user;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
| user | 0 | email | 1 | email | A | 1 | NULL | NULL | | BTREE | | |
| user | 0 | nick_name | 1 | nick_name | A | 1 | NULL | NULL | | BTREE | | |
| user | 0 | phone | 1 | phone | A | 1 | NULL | NULL | | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql>
mysql> desc user;
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| age | int(11) | YES | | 0 | |
| create_time | datetime | YES | | CURRENT_TIMESTAMP | |
| email | varchar(255) | NO | UNI | | |
| name | varchar(100) | NO | | NULL | |
| nick_name | varchar(200) | NO | UNI | NULL | |
| password | varchar(384) | NO | | NULL | |
| password_algorithm | varchar(20) | NO | | NULL | |
| password_salt | varchar(20) | NO | | NULL | |
| phone | varchar(20) | NO | UNI | | |
| sex | int(11) | YES | | NULL | |
| update_time | datetime | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
12 rows in set (0.01 sec)
添加索引：来自博客园
CREATE TABLE时：
添加索引1
mysql> CREATE TABLE table2(id bigint PRIMARY KEY, name varchar(50) UNIQUE, age int DEFAULT 0, introduction varchar(200) DEFAULT '',
-> INDEX idx_age (age DESC),
-> FULLTEXT idx_ft_intro (introduction ASC));
Query OK, 0 rows affected (0.30 sec)
mysql> show index from table2;
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table2 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| table2 | 0 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| table2 | 1 | idx_age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| table2 | 1 | idx_ft_intro | 1 | introduction | NULL | 0 | NULL | NULL | YES | FULLTEXT | | |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
ALTER TABLE时：
添加索引2
mysql> CREATE TABLE table3(id bigint, name varchar(50), age int DEFAULT 0, introduction varchar(200) DEFAULT '');
Query OK, 0 rows affected (0.02 sec)
# 没有任何索引
mysql> show index from table3;
Empty set (0.00 sec)
# 添加主键索引
mysql>
mysql> ALTER TABLE table3
-> ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> show index from table3;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table3 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql>
# 添加唯一索引
mysql> ALTER TABLE table3
-> ADD UNIQUE (name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 添加普通索引
mysql> ALTER TABLE table3
-> ADD INDEX idx_age (age DESC);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 添加全文索引
mysql> ALTER TABLE table3
-> ADD FULLTEXT ft1 (introduction);
Query OK, 0 rows affected, 1 warning (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql>
# 检查ALTER 添加的索引
mysql> show index from table3;
+--------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table3 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| table3 | 0 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| table3 | 1 | idx_age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| table3 | 1 | ft1 | 1 | introduction | NULL | 0 | NULL | NULL | YES | FULLTEXT | | |
+--------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql>
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX时：来自博客园
不能添加主键索引
添加复合索引：
添加索引3-复合
mysql> ALTER TABLE table3
-> ADD INDEX comp_idx_1 (name ASC, age DESC);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from table3;
+--------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table3 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| table3 | 0 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| table3 | 1 | idx_age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| table3 | 1 | comp_idx_1 | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| table3 | 1 | comp_idx_1 | 2 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| table3 | 1 | ft1 | 1 | introduction | NULL | 0 | NULL | NULL | YES | FULLTEXT | | |
+--------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
删除索引：
删除主键索引：
如果主键字段有AUTO_INCREMENT属性，需要先删除该属性。（少用）
ALTER TABLE 数据表 DROP PRIMARY KEY
删除非主键索引：来自博客园
ALTER TABLE 数据表 DROP INDEX 索引名
DROP INDEX 索引名 ON 数据表
删除索引
# 删除非主键索引
mysql> ALTER TABLE table3 DROP INDEX ft1;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP INDEX comp_idx_1 ON table3;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> show index from table3;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table3 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| table3 | 0 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| table3 | 1 | idx_age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
# 删除主键索引
# PRIMARY前后有 反引号（Tab键上面的按键），否则，失败
mysql> DROP INDEX PRIMARY ON table3;
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 'PRIMARY ON table3' at line 1
mysql>
mysql> DROP INDEX `PRIMARY` ON table3;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from table3;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table3 | 0 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| table3 | 1 | idx_age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
配置分为：基本配置，优化配置，还有高级配置——比如，集群配置等。
本文的Ubuntu使用apt安装，默认建立了 mysql用户及用户组，并将mysql添加到了自启动服务中。
命令mysql：链接MySQL服务器，/usr/bin/mysql
命令mysqld：启动服务器，/usr/sbin/mysqld
服务器配置文件：/etc/mysql中，，其中的 mysqld.cnf 是服务器配置文件
区段：来自博客园
[client] 不常用
[mysqld] 常用
[mysqld]配置：mysqld.cnf 中 及 额外的配置
| 配置 | 说明 |
| port | 服务器端口，默认3306 |
| datadir | 数据目录，默认/var/lib/mysql |
| bind-address | 服务器绑定的IP地址，默认注释掉 |
| max_connections | 客户端最大连接数，默认151（show variables like '%max_conn%';） |
| query_cache_limit | 查询缓存限制，默认1M |
| query_cache_size | 查询缓存大小，默认16M |
| log_error | 错误日志，默认/var/log/mysql/error.log |
| slow_query_log | 慢查询日志，默认1 |
| slow_query_log_file | 慢查询日志文件，/var/log/mysql/mysql-slow.log |
| long_query_time | 查询超过指定时间时，日志会记录到慢查询日志中，默认2（单位是 秒？） |
| log-queries-not-using-indexes | 是否在慢查询日志中记录 未使用索引查询 |
| log-bin | 二进制日志保存路径，主要用于 复制环境 和 数据恢复（重要） |
| max_binlog-size | 二进制日志单个文件的大小限制，默认100M |
| expire_logs_days | 自动删除超过指定天数的过期日志 |
| key_buffer_size | 索引缓冲区大小，由所有线程共享。增大可以更好处理索引。 |
| innodb_log_file_size | Redo log的空间通过innodb_log_file_size和innodb_log_files_in_group参数来调节。 |
| innodb_lock_wait_timeout | InnoDB的锁等待超时时间，默认50秒。 |
还有更多配置待解锁...来自博客园
1、《MySQL数据库原理、设计与应用》
作者：黑马程序员，2019年4月第1版
5、