# MySQL安装和基础数据类型
# 一,安装
这里介绍在 Linux
下的安装。
首先去官网下载 MySQL
的压缩包,官网地址 https://downloads.mysql.com/archives/community/,将下载好的 MySQL
安装包解压到 /usr/local
目录下,我这里使用的是 mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz
tar -zxvf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz -C /usr/local
进入刚才解压好的目录,可以看到有这么一个文件 INSTALL-BINARY
,这个其实就是 MySQL
的安装文档。下面根据这个安装文档进行操作安装:
# 1、安装依赖
yum install -y libaio
# 2、添加组
groupadd mysql
# 3、添加用户
useradd -r -g mysql mysql
# 4、创建软连接
进入 /usr/local
目录下
ln -s /usr/local/mysql-5.7.9-linux-glibc2.5-x86_64/ mysql
# 5、创建目录
mkdir mysql-files
# 6、授权
chmod 770 mysql-files
chown -R mysql .
2
# 7、切换组
chgrp -R mysql .
# 8、初始化
bin/mysqld --initialize --user=mysql
bin/mysql_ssl_rsa_setup # 这行命令会产生一个临时密码,注意记录,方便后面修改密码
2
# 9、继续授权
chown -R root .
chown -R mysql data mysql-files # 此时如果提示没有data文件夹就需要先创建一下,再执行这个命令
2
此时,进入目录 data
,如果发现目录里面有数据,就说明初始化成功了
# 10、启动
bin/mysqld_safe --user=mysql &
在这一步启动可能会报错,提示没有权限,此时就是读取配置文件 my.cnf
错误的问题。使用命令 /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
2
有可能这一步还是没法找到配置文件,那么去 /usr/local/mysql/support-files/
下找找是否有一个 my-default.cnf
文件,将这个文件复制到 /etc
下,并重命名为 my.cnf
。
# 11、查看是否启动成功
ps -ef | grep mysqld
出现如下:
root 20528 17748 0 23:05 pts/0 00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql 20610 20528 0 23:05 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/yjtravel-bring.err --pid-file=/usr/local/mysql/data/yjtravel-bring.pid
root 21277 17748 0 23:15 pts/0 00:00:00 grep --color=auto mysqld
2
3
说明启动成功了
# 12、配置服务默认开启
cp support-files/mysql.server /etc/init.d/mysql.server
# 13、查看目前配置的开机启动的程序
chkconfig --list
# 14、配置开机启动
chkconfig mysql.server on
# 15、配置环境变量
编辑文件 /etc/profile
,给文件末尾添加:
export PATH=/usr/local/mysql/bin:$PATH
刷新配置文件:
source /etc/profile
# 16、测试连接
mysql -uroot -p'MaZI.yGp*2Su' # 刚才安装时生成的密码
# 17、连接成功后,修改密码,配置允许远程连接
set password = '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
flush privileges;
2
3
%
表示所有 ip
# 18、重启mysql
先停止服务,然后再启动服务即可。
/etc/inint.d/mysql stop
/etc/inint.d/mysql start
2
# 二、多实例安装
首先修改配置文件如下:
[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
[mysqld1]
server-id=11
socket=/tmp/mysql.sock1
port=3307
datadir=/usr/local/mysql/data1
user=mysql
performance_schema=off
innodb_buffer_pool_size=32M
skip_name_resolve=1
log_error=error.log
pid-file=/usr/local/mysql/data1/mysql.pid1
[mysqld2]
server-id=12
socket=/tmp/mysql.sock2
port=3308
datadir=/usr/local/mysql/data2
user=mysql
performance_schema=off
innodb_buffer_pool_size=32M
skip_name_resolve=1
log_error=error.log
pid-file=/usr/local/mysql/data2/mysql.pid2
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
在 /usr/local/mysql
目录下创建 data1
和 data2
目录。并给予权限 chown mysql.mysql /usr/local/mysql/data1
和 chown mysql.mysql /usr/local/mysql/data2
接下来初始化;
mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data1
mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data2
2
同时不要忘记记录两个实例生成的密码。
接来下配置开机启动:
cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multid
然后修改 mysqld_multid
文件,在文件顶部加入环境变量:export PATH=$PATH:/usr/local/mysql/bin
然后再执行:
chkconfig mysqld_multid on
启动多实例:mysqld_multi start
查看启动状态:mysqld_multi report
,如果现实两个服务器都是 running
状态就说明成功了,如果不是 running
状态的话,可以去 /data1
或者 /data2
目录下查看下错误的日志信息,日志存放在 error.log
文件中。
至此,多实例的安装和启动就已经完成了,这个多实例和之前的单实例互相独立,互不影响。
接下来服务器登录两个实例进行修改密码和允许远程访问:
mysql -u root -S /tmp/mysql.sock1 -p -P3307
set password = '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
flush privileges;
2
3
然后重复这个操作对第二个实例进行操作。
# 三、MySQL权限
使用 root
用户通过工具远程登录,创建数据库 mall
,给用户 dev
授予查询 mall
数据库的权限:
GRANT SELECT ON mall.* TO 'dev'@'%' IDENTIFIED BY '123456'; # 这个密码可以和root用户的密码不一致
在执行这条语句的时候可能会提示 Access denied for user 'root'@'%' to database 'mall'
,这是由于多个 root
用户在登录的时候产生了多个进程,所以在开始给 root
用户授权的时候系统并不知道具体给哪个 root
授权,此时需要将所有root
相关进程进行结束,命令如下:
SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='root';
然后重新登录授权就没问题了。 具体原因可以查看这篇帖子:https://blog.csdn.net/gu_wen_jie/article/details/89242255
查看 dev
用户目前的权限:
SHOW GRANTS FOR 'dev'@'%'
# 1、权限粒度
其实在 MySQL
中,权限是可以精确到字段级别的。
创建表语句:
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
2
3
4
5
6
7
随便插入几条数据:
INSERT `account` VALUE ('1', 'lilei', '900');
INSERT `account` VALUE ('2', 'hanmei', '100');
INSERT `account` VALUE ('3', 'lucy', '250');
INSERT `account` VALUE ('5', 'tom', '0');
2
3
4
mysql
库中的 user
表存储了 用户+ip
所对应的权限。db
表存储了用户对库所具有的权限。tables_priv
和 columns_priv
可以控制表和列级别的权限。
移除给用户分配的权限:
REVOKE SELECT ON mall.* FROM 'dev'@'%';
只给用户分配指定字段的查询权限:
GRANT SELECT(id, name) ON mall.account to 'dev'@'%'; # 只能访问id和name字段
此时使用 dev
用户登录就只能访问 mall
数据库的 id
和 name
字段了。
# 2、角色概念
在 MySQL
中是没有明确的角色概念的,但是可以将用户理解成为一个角色的概念。
设置开启角色功能和密码加密:
SET GLOBAL check_proxy_users = 1;
SET GLOBAL mysql_native_password_proxy_users = 1;
2
创建用户(理解成角色)
CREATE USER 'dev_role'; // 看成是角色
CREATE USER 'deer'; // 看成是用户
CREATE USER 'enjoy'; // 看成是用户
2
3
此时需要给 root
用户授权的权限,需要在 MySQL
控制台进行授权,然后在进行授权。
GRANT PROXY ON ''@'' TO 'root'@'%';
GRANT PROXY ON 'dev_role' to 'deer';
GRANT PROXY ON 'dev_role' to 'enjoy';
flush privileges;
2
3
4
这时只要给 dev_role
角色授权,另外两个用户就自动有权限了。
GRANT SELECT(id, name) ON mall.account to 'dev_role'@'%';
这时使用 deer
和空密码进行登录,可以查询到 account
表的 id
和 name
字段了。
SELECT id, name FROM account;
在 proxies_priv
表中可以看到刚才给用户授予角色的信息。
# 四、MySQL数据类型
# 1、INT 类型
类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
有符号 TINYINT | 1 | -128 | 127 |
无符号 TINYINT | 1 | 0 | 255 |
有符号 SMALLINT | 2 | -32768 | 32767 |
无符号 SMALLINT | 2 | 0 | 65535 |
有符号 MEDIUMINT | 3 | -8388608 | 8388607 |
无符号 MEDIUMINT | 3 | 0 | 16777215 |
有符号 BIGINT | 8 | -92233720368547758080 | 92233720368547758087 |
无符号 BIGINT | 8 | 0 | 18446744073709551615 |
创建个无符号表,并插入数据:
CREATE TABLE test_unsigned (a INT UNSIGNED, b INT UNSIGNED);
INSERT test_unsigned VALUE(1, 2);
2
做如下查询:
SELECT b - a FROM test_unsigned;
可以正常查询,再做如下查询:
SELECT a - b FROM test_unsigned;
可以发现查询结果是null或者报错了,因为超出了最大精度。
创建表并插入数据:
CREATE TABLE test_int_n(a INT(4) ZEROFILL);
INSERT test_int_n VALUE(1);
INSERT test_int_n VALUE(123456);
2
3
4
这个INT(4)
表示如果没有达到长度位数4,会在前面补零。
此时使用终端进行查询:
SELECT * FROM test_int_n;
就会发现显示结果是:
+--------+
| a |
+--------+
| 0001 |
| 123456 |
+--------+
2
3
4
5
6
要想创建逐渐自动增长的表,一定要指明主键:
CREATE TABLE test_auto_increment(a INT AUTO_INCREMENT PRIMARY KEY);
插入数据:
INSERT test_auto_increment VALUES(null), (100), (null), (10), (null);
查询:
SELECT * FROM test_auto_increment;
结果:
1
10
100
101
102
2
3
4
5
再插入一条数据:
INSERT test_auto_increment VALUE(-1), (-3), (0);
查询
SELECT * FROM test_auto_increment;
结果:
-3
-1
1
10
100
101
102
104
2
3
4
5
6
7
8
# 2、字符类型
类型 | 说明 | N的含义 | 是否有字符集 | 最大长度 |
---|---|---|---|---|
CHAR(N) | 定长字符 | 字符 | 是 | 255 |
VARCHAR(N) | 变长字符 | 字符 | 是 | 16348 |
BINARY(N) | 定长二进制字节 | 字节 | 否 | 255 |
VARBINARY(N) | 变长二进制字节 | 字节 | 是 | 16348 |
TINYBLOB(N) | 二进制大对象 | 字节 | 是 | 256 |
BLOB(N) | 二进制大对象 | 字节 | 是 | 16K |
MEDIUMBLOB(N) | 二进制大对象 | 字节 | 是 | 255 |
LONGBLOB(N) | 二进制大对象 | 字节 | 否 | 4G |
TINYTEXT(N) | 大对象 | 字节 | 是 | 256 |
TEXT(N) | 大对象 | 字节 | 是 | 16K |
MEDIUMTEXT(N) | 大对象 | 字节 | 是 | 16M |
LONGTEXT(N) | 大对象 | 字节 | 是 | 4G |
除了 CHAR
和 VARCHAR
保存的是字符,其它保存的都是字节
默认情况下,当数据库的排序规则是 utf8_general_ci
的时候:
SELECT 'a' = 'A';
执行的结果是:
1
utf8_general_ci
排序规则会忽略大小写,而 utf8_bin
排序规则不会忽略大小写。
修改数据库的排序规则:
SET NAMES utf8mb4 COLLATE utf8mb4_bin;
此时再执行:
SELECT 'a' = 'A';
执行的结果是:
0
# 3、日期类型
日期类型 | 占用空间 | 表示范围 | |
---|---|---|---|
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 |
创建表,添加数据:
CREATE TABLE test_time(a TIMESTAMP, b DATETIME);
INSERT test_time VALUES (now(), now());
SELECT @@time_zone;
SET time_zone = '+00:00';
2
3
4
5
6
7
8
此时查询:
SELECT * FROM test_time;
结果:
2020-04-19 22:25:00 2020-04-19 22:25:00
如果修改了时区:
SET time_zone = '+00:00';
# 查询时区可以用这个
SET time_zone = '+00:00';
2
3
此时查询:
SELECT * FROM test_time;
结果:
2020-04-19 14:25:00 2020-04-19 22:25:00
发现时间不一样了,这说明 TIMESTAMP
类型是带有时区信息的,而 DATETIME
没有时区信息。
# 4、json类型
json
数据类型是从 mysql
5.7才引入的类型。
创建表:
CREATE TABLE json_user ( uid INT AUTO_INCREMENT, data json, PRIMARY KEY ( uid ) );
插入数据:
INSERT json_user
VALUES
( NULL, '{ "name":"lison", "age":18, "address":"enjoy"
}' );
INSERT json_user
VALUES
( NULL, '{"name":"james", "age":28, "mail":"james@163.com"
}' );
2
3
4
5
6
7
8
这里如果 json
格式有问题,是无法插入成功的。
# (1) 常用函数
# json_extract 抽取
SELECT json_extract ( '[10, 20, [30, 40]]', '$[1]' );
执行结果是:20
如果想要查询 json_user
表中 data
字段 json
的 name
列和 address
列,可以这么写:
SELECT
json_extract ( data, '$.name' ),
json_extract ( data, '$.address' )
FROM
json_user;
2
3
4
5
# json_object 对象转json
这个函数会将给定的参数两两匹配,组成一个 json
对象。
SELECT json_object ( "name", "enjoy", "email", "enjoy.com", "age", 35 );
执行结果是:{"age": 35, "name": "enjoy", "email": "enjoy.com"}
给 json_user
表添加数据:
INSERT json_user
VALUE
( NULL, json_object ( "name", "enjoy", "email", "enjoy.com", "age", 35 ) );
2
3
# json_insert 插入数据
首先讲一个知识点。@
和 @@
,前者是查询局部变量的,后者是查询全局变量的。
例如 SHOW VARIABLES LIKE '%datadir%';
可以查询存放数据的目录,同样使用 SELECT @@datadir;
也可以完成同样的效果。
那么此时设置一个局部变量:
SET @json = '{ "a": 1, "b": [2, 3]}';
然后:
SELECT json_insert ( @json, '$.a', 10, '$.c', '[true, false]' );
执行结果是:{"a": 1, "b": [2, 3], "c": "[true, false]"}
。
因为原本的 @json
中,a
字段已经有值了,所以插入的时候10是不能插入成功的,而字段 c
因为在原本的 json
中不存在,所以可以将 c
字段追加插入到 json
中。
看下面这个语句:
UPDATE json_user
SET data = json_insert ( data, "$.address_2", "xiangxue" )
WHERE
uid = 1;
2
3
4
这个就给原来 data
字段的 json
中新增了一个 address_2
字段,并且值是 xiangxue
# json_merge 合并数据并返回
SELECT json_merge ( '{"name": "enjoy"}', '{"id": 47}' );
执行结果:{"id": 47, "name": "enjoy"}
那么语句:
SELECT
json_merge ( json_extract ( data, '$.address' ), json_extract ( data, '$.address_2' ) )
FROM
json_user
WHERE
uid = 1;
2
3
4
5
6
的意思就是将 data
字段 json
中的 address
和 address2
字段进行合并并返回。
结果:["enjoy", "xiangxue"]
其它关于 json
的更多函数可以在官网查询:https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
# (2) json索引
json
类型数据本身无法直接创建索引,需要将需要索引的 json
数据重新生成虚拟列(Virtual Columns
)之后,对该列进行索引。
创建表:
CREATE TABLE test_index_1 (
data json,
gen_col VARCHAR ( 10 ) generated always AS ( json_extract ( data, '$.name' ) ),
INDEX idx ( gen_col )
);
2
3
4
5
这个语句创建的表有2个字段,一个字段是 data
,另一个字段 gen_col
的值是来自 data
字段中 name
字段的值。
插入数据:
INSERT test_index_1 ( data )
VALUES
( '{"name":"king", "age":18, "address":"cs"}' );
INSERT test_index_1 ( DATA )
VALUES
( '{"name":"peter", "age":28, "address":"zz"}' );
2
3
4
5
6
查询即可看到 gen_col
字段就有了值。
于是,也就可以实现 where
语句的查询功能:
SELECT
json_extract ( data, "$.name" ) AS username
FROM
test_index_1
WHERE
gen_col = '"king"';
2
3
4
5
6
注意,这里生成的 gen_col
列的值是带有 "
的,查询的时候不要忘记了。但是这样似乎有些麻烦,有什么办法可以让查询的时候不用输入 "
呢?
只需要在创建的时候使用如下语句:
CREATE TABLE test_index_2 (
data json,
gen_col VARCHAR ( 10 ) generated always AS ( json_unquote( json_extract( DATA, "$.name" ) )),
INDEX idx ( gen_col )
);
2
3
4
5
这里使用到了 json_unquote()
函数,它将 "
去掉了。