MySql 重要性质
什么都不说，先看张图：https://db-engines.com/en/ranking
在所有数据库中，MySql 排在第二，而 nosql 中 mongodb 排在第一，你可能在想是不是有必要把 Oracle 也学习下，别着急，再看张图:
全球访问量最大的 20 家网站，他们分别使用了什么数据库呢，绝大多数使用 mysql,有两个完整 live.com 和 bing 使用的是 mssql，并不是他们使用不了 mysql，而是他要支持自己的数据库。
在国外可能挺多使用 mssql 或者 oracle 的，但是在过去，在去 IOE 的大背景下，包括银行在内的很多传统公司慢慢都在向mysql 转型，不过其中有个老大不掉的公司，中国电力，依然使用 oracle,在十年的时间仅仅在 oracle 的使用上，中国电力就支出 390 几个亿，平均一年30，40 个亿，只能说他有钱。
MySQL 安装
准备工作
Linux 使用的版本是 centos 7,为方便起见，先把防火墙关闭，配置好网络，在安装部分，会分成两部分讲，首先讲单实例安装，也就是一台服务器上就装一个 mysql，接下来就多实例安装，在一个服务器上安装 2 个甚至多个 mysql。
单实例安装
cp /soft/mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz /usr/local/
解压 mysql 到/usr/local 目录
解压：tar -zxvf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz
安装需要的依赖：yum install -y libaio
具体安装
shell> groupadd mysql
shell> useradd -r -g mysql mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chmod 770 mysql-files
shell> chown -R mysql .
shell> chgrp -R mysql .
在mysql目录下 mkdir data文件夹，然后参照下面的etc移除在执行以下指令。
shell> bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and up
shell> bin/mysql_ssl_rsa_setup # MySQL 5.7.6 and up
shell> chown -R root . shell> chown -R mysql data mysql-files
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
配置环境变量：
export PATH=/usr/local/mysql/bin:$PATH
配置开启启动
chkconfig mysql.server on
chkconfig --list
登陆，修改密码
set password = 'root1234%';
允许远程登陆
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root1234%' WITH GRANT OPTION;
flush privileges;
启动的时候可能会报错
这是因为 mysql 启动的时候需要配置文件，而在安装 centos 的时候，哪怕是 mini 版本都会有个默认的配置在/etc 目录中。
/usr/local/mysql/bin/mysqld --verbose --help |grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
Mysql 启动的时候会以上面所述的顺序加载配置文件；
如果报错，先重命名 my.cnf 文件。
mv my.cnf my.cnfbak 移除my.cnf，保留一个备份。
多实例安装
以前一些很 low 的方法是，解压两个 mysql，分别放到不同文件夹，其实在 mysql 中已经考虑到了多实例安装的情况。也有相应的脚本命令的支持。
现在要求装两个 mysql 一个 3307，3308；
新建 /etc/my.cnf 配置如下:
[mysqld]
sql_mode ="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /var/log/mysqld_multi.log
user= root
pass= xxxxxxxx （官方文档给的password，但是使用password命令无法生效）
[mysqld1]
server-id = 11
socket = /tmp/mysql.sock1
port = 3307
datadir = /data1
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /data1/mysql.pid1
[mysqld2]
server-id = 12
socket = /tmp/mysql.sock2
port = 3308
datadir = /data2
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /data2/mysql.pid2
创建 2 个数据目录
mkdir /data1
mkdir /data2
chown mysql.mysql /data{1..2}
mysqld --initialize --user=mysql --datadir=/data1
mysqld --initialize --user=mysql --datadir=/data2
cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multid
配置开机启动
chkconfig mysqld_multid on
这个命令使用之后开机如果没有启动，需要配置一个脚本：
查看状态
mysqld_multi report
这个时候发现还需要 perl 的环境，安装：yum -y install perl perl-devel
在运行，发现已经有实例了：mysqld_multi report
mysqld_multi start：启动，分别修改密码，允许远程连接
mysql -u root -S /tmp/mysql.sock1 -p -P3307
mysql -u root -S /tmp/mysql.sock2 -p -P3308
set password = 'root1234%';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root1234%' WITH GRANT OPTION;
flush privileges;
最简单的 MySql 权限
最简单也是最高效的，如果解决新手们删库跑路的问题其实也是很简单的，对于正式库只给一个增删改查的权限，或者只给一个查询权限（是不是就解决了删库的可能性？）。
以下内容如果看官是大牛，请稍安勿躁，内容的方式是从简单到入门，从入门到进阶，从进阶到实战，从实战到...(包你满意)。
使用 Root 用户，执行
grant SELECT on mall.* TO 'dev'@'192.168.244.%' IDENTIFIED BY '123' WITH GRANT OPTION;
很简单的一句 sql，创建了一个 dev 的用户，密码为 123，仅仅运行在网段为 192.168.0.*的网段进行查询操作。
再执行一条命令：show grants for 'dev'@'192.168.244.%'
不错，可以连接，也可以执行 select，这个时候还想删库？
深入研究下 MySQL 权限
用户标识是什么
上面一句简单的 SQL 堪称完美的解决了程序员新手的删库跑路的问题，高兴吧，你学到了新姿势，但是如果想面试给面试管留下好映像，上面的知识好像还不够，有必要好好深入研究下 MySql 的权限了。
这里有个小的知识点需要先具备，在 mysql 中的权限不是单纯的赋予给用户的，而是赋予给” 用户+IP”的；
比如 dev 用户是否能登陆，用什么密码登陆，并且能访问什么数据库等都需要加上 IP，这样才 算 一 个 完 整 的 用 户 标 识 ， 换 句 话 说 'dev'@'192.168.0.168' 、 'dev'@'127.0.0.1' 与'dev'@'localhost' 这 3 个是完全不同的用户标识（哪怕你本机的 ip 就是 192.168.0.168）。
用户权限所涉及的表
有了用户标识的概念接下来就可以看权限涉及的表了，这也是面试的时候加分项哦。
有几张表你可以好好的记记的，mysql.user，mysql.db，mysql.table_priv，mysql_column_priv；
你可以熟悉其中的 user 表，甚至手动的改过里面的数据（不合规范哦！）。
那这些表有什么用，和权限又有什么关系呢？
User 的一行记录代表一个用户标识
db 的一行记录代表对数据库的权限
table_priv 的一行记录代表对表的权限
column_priv 的一行记录代表对某一列的权限
新建一个表 account
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int(11) NOT NULL, `name` varchar(50) DEFAULT NULL, `balance` int(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_balance` (`balance`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `account` VALUES ('1', 'lilei', '900');
INSERT INTO `account` VALUES ('2', 'hanmei', '100');
INSERT INTO `account` VALUES ('3', 'lucy', '250');
INSERT INTO `account` VALUES ('5', 'tom', '0');
很诧异吧，mysql 其实权限并不是特别 low，权限的粒度甚至到了某一列上，举例来说，有个表 account 表；
对于前面创建的 dev 用户我不想让他访问 balance 列,但是 id 和 name 列是可以访问的，这样的需求在工作中不是没有。
grant select(id,name) on mall.account to 'dev'@'192.168.244.%';
这时候可以在分别看下 table_priv，column_priv 的数据：
这个就应该豁然开朗了吧；
REVOKE SELECT on mall.* from 'dev'@'192.168.244.%'。
你使用 dev 登陆查询试试：
你再要查询所有记录？不好意思不让查；
而你查询 id，name 查询又是可以了。
Mysql 的角色
准备工作
MySql 基于”用户+IP”的这种授权模式其实还是挺好用的，但如果你使用 Oracle、PostgreSQL、SqlServer 你可能会发牢骚：这样对于每个用户都要赋权的方式是不是太麻烦了，如果我用户多呢？有没有角色或者用户组这样的功能呢？
好吧，你戳中了 mysql 的软肋，很痛，在 mysql5.7 开始才正式支持这个功能，而且连 mysql官方把它叫做“Role Like”（不是角色，长得比较像而已，额~~~）？
好咧，那在 5.7 中怎么玩这个不像角色的角色呢？
show variables like "%proxy%"；
你得先把 check_proxy_users，mysql_native_password_proxy_users 这两个变量设置成 true 才行；
set GLOBAL check_proxy_users =1;
set GLOBAL mysql_native_password_proxy_users = 1;
当然，你也可以把这两个配置设置到 my.cnf 中。
创建一个角色
create USER 'dev_role' ；可能被你发现了，我这里创建得是个 user,为了稍微像角色一点点，我给这 user 取名叫dev_role，而且为了方便也没用使用密码了。
创建 2 个开发人员账号
create USER 'deer'
create USER 'enjoy' ；
这两个用户我也没设置密码
把两个用户加到组里面
grant proxy on 'dev_role' to 'deer'
grant proxy on 'dev_role' to 'enjoy'
可以看下其中一个用户的权限
这里有个小的地方需要注意，如果你是远程连接，你可能会收获一个大大的错误，你没有权限做这一步，这个时候你需要再服务器上执行一条：GRANT PROXY ON ''@'' TO 'root'@'%' WITH GRANT OPTION;
给角色 dev_role 应该有的权限
有了用户了，这用户也归属到了 dev_role 这角色下面，那接下来要做的就很简单了，根据业务需求给这角色设置权限就好了：grant select(id,name) on mall.account to 'dev_role'
测试
好咧，大功告成，现在使用'deer'用户登陆系统试试
select id ,name from mall.account；
学到了吧，涨到姿势了吧，你可能又会问，这种角色的权限是存哪的呢？
给你看表：
MySql 数据类型
Int 类型
有无符号
在项目中使用 BIGINT，而且是有符号的。
演示：
create table test_unsigned(a int unsigned, b int unsigned);
insert into test_unsigned values(1, 2);
select b - a from test_unsigned;
select a - b from test_unsigned; --运行出错
INT(N)是什么？
演示：
create table test_int_n(a int(4) zerofill);
insert into test_int_n values(1);
insert into test_int_n values(123456);
int(N)中的 N 是显示宽度，不表示存储的数字的长度的上限
zerofill 表示当存储的数字长度 < N 时，用数字0填充左边，直至补满长度 N
当存储数字的长度 超过 N 时 ，按照 实际存储 的数字显示
自动增长的面试题
这列语法有错误吗？
create table test_auto_increment(a int auto_increment); --执行错误
create table test_auto_increment(a int auto_increment primary key); --只能在主键上写入自动增长
以下结果是什么？
insert into test_auto_increment values(NULL); - -1
insert into test_auto_increment values(0); - -插入不进
insert into test_auto_increment values(-1); - - 负1
insert into test_auto_increment values(null),(100),(null),(10),(null)；- - 1,10,100,101,102
字符类型
排序规则
select 'a' = 'A'; - -1
create table test_ci (a varchar(10), key(a));
insert into test_ci values('a');
insert into test_ci values('A');
select * from test_ci where a = 'a'; --结果是什么？
ci :表示不区分大小写
bin :区分大小写
set names utf8mb4 collate utf8mb4_bin；--设置之后就会区分大小写
时间类型
日期类型 占用空间 表示范围
DATETIME 8 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE 3 1000-01-01 ~ 9999-12-31
TIMESTAMP 4 1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC
YEAR 1 YEAR(2):1970-2070, YEAR(4):1901-2155
TIME 3 -838:59:59 ~ 838:59:59
datatime 与 timestamp 区别
演示:
create table test_time(a timestamp, b datetime);
insert into test_time values (now(), now());
select * from test_time;
select @@time_zone;
set time_zone='+00:00';
select * from test_time;
timestamp更改时区会发生改变，datatime则不会发生改变。
JSON 类型
JSON 入门
新建表
create table json_user (uid int auto_increment, data json, primary key(uid));
插入数据
insert into json_user values (
null, '{ "name":"lison", "age":18, "address":"enjoy"}' );
insert into json_user values (null,
'{"name":"james", "age":28, "mail":"james@163.com"
}');
JSON 函数
json_extract 抽取
select json_extract('[10, 20, [30, 40]]', '$[1]'); --20
select
json_extract (data, '$.name'),json_extract(data, '$.address') from json_user;
JSON_OBJECT 将对象转为 json
select json_object("name", "enjoy", "email", "enjoy.com", "age",35);
insert into json_user values (null,
json_object("name", "enjoy", "email", "enjoy.com", "age",35) );
json_insert 插入数据
语法：JSON_INSERT(json_doc, path, val[, path, val] ...)
set @json = '{ "a": 1, "b": [2, 3]}';
select json_insert(@json, '$.a', 10, '$.c', '[true, false]');
update json_user set data = json_insert(data, "$.address_2", "xiangxue") where uid = 1;
json_merge 合并数据并返回
select json_merge('{"name": "enjoy"}', '{"id": 47}');
select
json_merge(
json_extract(data, '$.address'),
json_extract(data, '$.address_2'))
from json_user where uid = 1;
其他函数：
https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html 。
JSON 索引
JSON 类型数据本身 无法直接 创建索引，需要将需要索引的 JSON 数据 重新生成虚拟列(Virtual Columns) 之后，对该列进行索引。
create table test_inex_1(
data json, gen_col varchar(10) generated always as (json_extract(data, '$.name')),
index idx (gen_col)
);
insert into test_inex_1(data) values ('{"name":"king", "age":18, "address":"cs"}');
insert into test_inex_1(data) values ('{"name":"peter", "age":28, "address":"zz"}');
select * from test_inex_1;
疑问：这条 sql 查询的结果是？
select json_extract(data,"$.name") as username from test_inex_1 where gen_col="king"; --0
select json_extract(data,"$.name") as username from test_inex_1 where gen_col='"king"';
explain select json_extract(data,"$.name") as username from test_index_1 where gen_col="king" ；
查阅官方文档，建立虚拟列，这个列查询的时候不需要加上“”符号
create table test_index_2 (
data json, gen_col varchar(10) generated always as (
json_unquote(
json_extract(data, "$.name")
)), key idx(gen_col)
);
insert into test_index_2(data) values ('{"name":"king", "age":18, "address":"cs"}');
insert into test_index_2(data) values ('{"name":"peter", "age":28, "address":"zz"}');
select json_extract(data,"$.name") as username from test_index_2 where gen_col="king";
网友评论