MySQL 安装配置与常用命令速查手册，涵盖用户管理、表操作、索引优化、存储过程等核心用法。

安装配置

Windows 安装

1. 解压 MySQL.zip 后，添加 bin 目录到环境变量

2. 配置 my.ini 文件：

   basedir = D:\mysql-5.6.24-winx64
   datadir = D:\mysql-5.6.24-winx64\data

3. 安装服务（以管理员身份运行 cmd）：

   mysqld -install    # 添加到服务
   mysqld -remove     # 移出服务
   net start mysql    # 启动服务

4. 登录并修改用户信息：

   mysql -u root -p

   -- 更改用户名
   USE mysql;
   UPDATE user SET user="newUserName" WHERE user="oldUserName";
   FLUSH PRIVILEGES;
   EXIT;
   
   -- 修改密码
   mysqladmin -u "username" -p password "newPassword"

5. 环境变量配置：

   变量名	值
   MYSQL_HOME	D:\mysql-5.6.24-winx64
   Path	添加 %MYSQL_HOME%\bin

Mac 安装

1. 设置命令别名（避免每次切换目录）：

   alias mysql=/usr/local/mysql/bin/mysql
   alias mysqladmin=/usr/local/mysql/bin/mysqladmin

2. 登录 MySQL：

   mysql -u root -p

Linux 安装

1. 下载并解压：

   wget https://dev.mysql.com//Downloads/MySQL-5.7/mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz
   tar zxvf mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz
   mv mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz mysql

2. 配置字符集：

   cd /usr/local/mysql/support-files/
   cp my-default.cnf /etc/my.cnf
   vi /etc/my.cnf

   [mysqld]
   character-set-client-handshake = FALSE
   character-set-server = utf8mb4
   collation-server = utf8mb4_unicode_ci
   
   [client]
   default-character-set = utf8mb4

3. 配置开机启动：

   cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
   vi /etc/init.d/mysql
   # 设置 basedir=/usr/local/mysql
   # 设置 datadir=/usr/local/mysql/data

4. 创建用户并授权：

   groupadd mysql
   useradd -r -g mysql mysql
   passwd mysql
   chown -R mysql:mysql /usr/local/mysql/

5. 初始化并启动：

   ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
   ./mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
   ./mysql -uroot -p

6. 修改密码并授权远程访问：

   SET password=password('新密码');
   GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY '新密码';
   FLUSH PRIVILEGES;

用户与权限管理

用户操作

-- 查询所有用户
SELECT USER, HOST FROM MYSQL.USER;

-- 创建用户
CREATE USER 'O2O'@'localhost' IDENTIFIED BY 'O2O';

-- 删除用户
DROP USER O2O;

权限授予

GRANT ALL PRIVILEGES ON *.* TO 'O2O'@'localhost';
FLUSH PRIVILEGES;

数据库操作

实例管理

CREATE DATABASE o2o;
SHOW DATABASES;
DROP DATABASE IF EXISTS o2o;

数据导入导出

先创建软链接：

ln -fs /usr/local/mysql/bin/mysqldump /usr/bin
ln -fs /usr/local/mysql/bin/mysql /usr/bin

导出与导入命令：

操作	命令
导出表和数据	mysqldump -h localhost -u root -p dbname > backup.sql
仅导出表结构	mysqldump -h localhost -u root -p -d dbname > schema.sql
导入数据	mysql -u root -p dbname < backup.sql

表与索引

表空间创建

CREATE TABLESPACE TBSPACE_O2O
ADD DATAFILE 'TBSPACE_O2O.ibd'
USE LOGFILE GROUP LOGGROUP_O2O
EXTENT_SIZE = 100M
INITIAL_SIZE = 3072M
AUTOEXTEND_SIZE = 100M;

表创建示例

USE O2O;

CREATE TABLE `TB_AREA` (
  `AREA_ID` INT(2) NOT NULL AUTO_INCREMENT,
  `AREA_NAME` VARCHAR(200) NOT NULL,
  `PRIORITY` INT(2) NOT NULL,
  `CREATE_TIME` DATETIME DEFAULT NULL,
  `LAST_EDIT_TIME` DATETIME DEFAULT NULL,
  PRIMARY KEY(`AREA_ID`),
  UNIQUE KEY `UK_AREA`(`AREA_NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

存储引擎选择：

引擎	特点	适用场景
InnoDB	多线程、行级锁	高并发写入
MyISAM	单线程、表级锁	读密集型应用

索引操作

-- 分析索引命中
EXPLAIN SELECT * FROM table WHERE a=1 AND b=2;

-- 更新索引统计信息
ANALYZE TABLE table_name;

-- 查看索引
SHOW INDEX FROM table_name;

-- 创建索引
ALTER TABLE table_name ADD INDEX idx_ab(a, b);

-- 删除索引
ALTER TABLE table_name DROP INDEX idx_ab;

-- 忽略索引
SELECT * FROM table IGNORE INDEX (idx_a) WHERE a=1 AND b=2;

-- 强制使用索引
SELECT * FROM table FORCE INDEX (idx_a) WHERE a=1 AND b=2;

索引设计原则：

• 将选择性高的列放在前面（Cardinality 值越大选择性越高）
• 列顺序优先级：等值 > 排序 > 范围
• 子句优先级：WHERE > JOIN

函数与存储过程

自定义函数

CREATE DEFINER=`root`@`%` FUNCTION `GET_ALL_COMPANY_NODE`()
RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
  DECLARE V_RESULT VARCHAR(1000);
  RETURN V_RESULT;
END;

存储过程

-- 创建存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `GEN_PERSION_MANAGE_STATISTICS`()
BEGIN
  DECLARE V_PARAM1 VARCHAR(32);
  DECLARE V_PARAM2 INT;
END;

-- 执行存储过程
CALL GEN_PERSION_MANAGE_STATISTICS();

-- 调试存储过程
SET @num = 0;
CALL GEN_PERSION_MANAGE_STATISTICS_6();
SELECT @num;

内置函数速查

函数类别	函数名	说明
日期时间	CURDATE()	当前日期
	SYSDATE()	当前时间
	YEAR(CURDATE())	当前年份
	MONTH(CURDATE())	当前月份
类型转换	CONVERT(str, SIGNED)	字符串转数字
	ROUND(num, 2)	保留 2 位小数
JSON 操作	JSON_UNQUOTE(JSON_EXTRACT(json, '$.id'))	JSON 转字符串
	CONCAT('[{"id":"', val, '"}]')	字符串转 JSON
字符串处理	REPLACE(str, '-', '')	替换字符
	STR_TO_DATE('2019-01-20', '%Y-%m-%d')	字符串转时间
	DATE_FORMAT(date, '%Y-%m-%d')	时间转字符串
聚合函数	GROUP_CONCAT(id)	分组拼接

定时任务与游标

定时任务

-- 创建定时任务
CREATE EVENT `cloudpivot`.`EVENT_GPMS`
ON SCHEDULE EVERY '1' MONTH STARTS '2019-12-01 00:00:00'
DO CALL GEN_PERSION_MANAGE_STATISTICS();

-- 查看定时状态
SHOW VARIABLES LIKE '%sche%';

-- 开启/关闭定时
SET GLOBAL event_scheduler = 1;
ALTER EVENT EVENT_GPMS ON COMPLETION PRESERVE ENABLE;
ALTER EVENT EVENT_GPMS ON COMPLETION PRESERVE DISABLE;

游标使用

DECLARE S INT DEFAULT 0;
DECLARE V_COMPANY_ID VARCHAR(32);
DECLARE V_COMPANY_IDS CURSOR FOR
  SELECT T1.ID FROM h_org_department T1
  WHERE FIND_IN_SET(id, GET_ALL_COMPANY_NODE());
DECLARE CONTINUE HANDLER FOR NOT FOUND SET S=1;

OPEN V_COMPANY_IDS;
FETCH V_COMPANY_IDS INTO V_COMPANY_ID;
WHILE S<>1 DO
  SET V_RESULT = CONCAT(V_RESULT, ",", V_COMPANY_ID);
  FETCH V_COMPANY_IDS INTO V_COMPANY_ID;
END WHILE;
CLOSE V_COMPANY_IDS;

服务管理命令

操作	命令
登录	mysql -u root -p
启动	service mysqld start
重启	service mysqld restart
停止	service mysqld stop

线程管理

-- 查找活跃事务
SELECT * FROM information_schema.innodb_trx;

-- 杀死线程
KILL 78879;

常用查询技巧

查询表字段名

纵表形式：

SELECT CONCAT('`', column_name, '`,')
FROM information_schema.columns
WHERE table_name='ic8x6_EmployeeRosters' AND table_schema='cloudpivot';

横表形式：

SELECT GROUP_CONCAT(t1.`NAME`)
FROM (
  SELECT CONCAT('`', column_name, '`') AS `NAME`, '1' AS `TAG`
  FROM information_schema.columns
  WHERE table_name='ij3ak_employeerosters' AND table_schema='cloudpivots'
) t1 GROUP BY t1.TAG;

查询所有表

SELECT table_name
FROM information_schema.tables
WHERE table_schema='cloudpivot' AND table_name LIKE '%POST%';

时间区间查询

查询上个月的数据：

SELECT * FROM table_name
WHERE create_time
BETWEEN DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), INTERVAL -DAY(CURDATE())+1 DAY)
AND DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), INTERVAL -DAY(CURDATE())+1 DAY), INTERVAL 1 MONTH);

清除数据中的空白字符

UPDATE ij3ak_salaryfiles
SET type = REPLACE(REPLACE(REPLACE(REPLACE(type, CHAR(13), ''), CHAR(10), ''), CHAR(9), ''), ' ', '');

时间间隔计算

计算类型	SQL 示例
秒	CEIL((SYSDATE - transdate) * 24 * 60 * 60)
分钟	CEIL((SYSDATE - transdate) * 24 * 60)
小时	CEIL((SYSDATE - transdate) * 24)
天	CEIL(SYSDATE - transdate)
月	TRUNC(MONTHS_BETWEEN(SYSDATE, transdate))
年	TRUNC(MONTHS_BETWEEN(SYSDATE, transdate) / 12)

总结

本文档整理了 MySQL 常用命令，涵盖：

• 多平台安装配置流程
• 用户权限管理
• 数据库与表操作
• 索引优化技巧
• 函数与存储过程
• 定时任务与游标
• 常用查询技巧

参考资源

• MySQL 官方文档
• MySQL 8.0 Reference Manual

贡献者: 靳琛

上一页photoshop

下一页Oracle 数据库常用命令速查手册