mysql 知识点

[mysterin]

创建数据库

create database xxx default character set utf8 collate utf8_general_ci;

[mysterin]

找到 mysql 配置文件路径

1. ps aux | grep mysql | grep my.cnf
   有输出表示加载指定配置文件, 没有说明使用默认配置
2. mysql --help | grep my.cnf
   展示加载默认配置文件的路径

修改 mysql 不区分大小写

找到配置文件, 在 [mysqld] 下增加 lower_case_table_names=1

[mysterin]

设置不使用 ONLY_FULL_GROUP_BY 模式

在配置文件中 [mysqld] 下增加
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysterin]

修改时区

在配置文件中 [mysqld] 下增加
default-time_zone = '+8:00'

[mysterin]

删除数据库

drop database xxx;

选择数据库

use xxx;

[mysterin]

mysql 数字类型

分无符号和有符号, 其中无符号的范围是: 0~2 的字节数次幂 -1; 有符号范围是: -2 的字节数减一次幂 ~ 2 的字节数减一次幂 -1

类型	字节
tinyint	1
smallint	2
mediumint	3
int	4
bigint	8
float	4
double	8
decimal	DECIMAL(M, D), M D 最大值+2

mysql 日期类型

类型	字节	范围	格式
date	3	1000-01-01 ~ 9999-12-31	YYYY-MM-DD
time	3	-838:59:59 ~ 838:59:59	HH:MM:SS
year	1	1901	2155
datetime	8	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS
timestamp	4	1970-01-01 00:00:00 ~ 2038	YYYY-MM-DD HH:MM:SS

mysql 字符串类型

类型	字节	用途
char	0-255	定长字符串
varchar	0-65535	变长字符串
tinyblob	0-255	不超过 255 字节的二进制数据
tinytext	0-255	短文本字符串
blob	0-65535	长文本二进制数据
text	0-65535	长文本数据
mediumblob	0-16777215	中等文本二进制数据
mediumtext	0-16777215	中等长度文本数据
longblob	0-4294967295	二进制的超大数据
longtext	0-4294967295	超长文本数据

[mysterin]

char, varchar, text 区别

1. char(n), varchar(n) 定义时, n 是表示字符数, 因此要注意对应字节数不能超过最大字节数;
2. char 是定长, 超出部分会截断, 比如:

字符值	char(4)	字节
abc	abc	4
abcd	abcd	4
abcde	abcd	4

3. varchar 是变长, 超出部分同样会截断, 而且需要 1-3 个字节保存长度, 比如:

字符值	varchar(4)	字节
ab	ab	3
abcd	abcd	5
abcde	abcd	5

4. text 跟 varchar 类似, 只不过不需要字节保存长度, 可以用全部字节保存数据;
5. varchar 大于 varchar(255)会变成 tinytext 类型; 大于 varchar(500) 会变成 text 类型; 大于 varchar(20000) 变成 mediumtext 类型, 因此过长的 varchar 跟 text 没区别;

[mysterin]

mysqldump 条件导出

mysqldump -uroot -p123456 cmdb em_event --where="sys_created_on>'2018-11-19'" > ./em_event.sql

[mysterin]

授权允许远程登录

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

还需要修改配置文件， 把 bind-address=127.0.0.1 注释掉：

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 

[mysterin]

使用 navicat 登录 mysql8 报 caching_sha2_password cannot be loaded

原因是客户端不支持 caching_sha2_password 密码加密方式, 改为 mysql_native_password

use mysql;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
select host, user, authentication_string, plugin from user;

[mysterin]

不使用缓存查询

select sql_no_cache * from xxx

[mysterin]

创建时间和更新时间

CREATE TABLE person_info (
id INT(11) NOT NULL AUTO_INCREMENT,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
modify_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
username VARCHAR(255) NOT NULL,
age INT(11) NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入示例:

INSERT INTO person_info (username, age) VALUES ('lin', 22),('bin', 21)

插入结果:

id	create_time	modify_time	username	age
1	2018-01-16 10:29:12	2018-01-16 10:29:12	lin	22
2	2018-01-16 10:29:12	2018-01-16 10:29:12	bin	21

更新示例:

UPDATE person_info SET age=23 WHERE username='bin'

更新结果:

id	create_time	modify_time	username	age
1	2018-01-16 10:29:12	2018-01-16 10:29:12	lin	22
2	2018-01-16 10:29:12	2018-01-16 10:32:12	bin	23

[mysterin]

创建索引

CREATE INDEX idx_id ON person_info (id)

或者

ALTER TABLE person_info ADD INDEX idx_id (id)

创建唯一索引

CREATE UNIQUE INDEX idx_id ON person_info (id)

或者

ALTER TABLE person_info ADD UNIQUE INDEX idx_id (id)

删除索引

DROP INDEX idx_id ON person_info

或者

ALTER TABLE person_info DROP INDEX idx_id

[mysterin]

新增列

ALTER TABLE gzyd_alarm_info ADD COLUMN ttt VARCHAR(30) NOT NULL

删除列

ALTER TABLE gzyd_alarm_info DROP COLUMN ttt