查询MySQL版本
show variables like '%version%';
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.27 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.27 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | macos10.14 |
+-------------------------+------------------------------+
mysql的utf8mb4编码的collation
SHOW COLLATION WHERE charset = 'utf8mb4';
查询结果
一般是utf8mb4_general_ci和utf8mb4_bin,前者排序时不区分大小写。
collation | charset | id | default | compiled | sortlen |
---|---|---|---|---|---|
utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
utf8mb4_bin | utf8mb4 | 46 | Yes | 1 | |
utf8mb4_unicode_ci | utf8mb4 | 224 | Yes | 8 | |
utf8mb4_icelandic_ci | utf8mb4 | 225 | Yes | 8 | |
utf8mb4_latvian_ci | utf8mb4 | 226 | Yes | 8 | |
utf8mb4_romanian_ci | utf8mb4 | 227 | Yes | 8 | |
utf8mb4_slovenian_ci | utf8mb4 | 228 | Yes | 8 | |
utf8mb4_polish_ci | utf8mb4 | 229 | Yes | 8 | |
utf8mb4_estonian_ci | utf8mb4 | 230 | Yes | 8 | |
utf8mb4_spanish_ci | utf8mb4 | 231 | Yes | 8 | |
utf8mb4_swedish_ci | utf8mb4 | 232 | Yes | 8 | |
utf8mb4_turkish_ci | utf8mb4 | 233 | Yes | 8 | |
utf8mb4_czech_ci | utf8mb4 | 234 | Yes | 8 | |
utf8mb4_danish_ci | utf8mb4 | 235 | Yes | 8 | |
utf8mb4_lithuanian_ci | utf8mb4 | 236 | Yes | 8 | |
utf8mb4_slovak_ci | utf8mb4 | 237 | Yes | 8 | |
utf8mb4_spanish2_ci | utf8mb4 | 238 | Yes | 8 | |
utf8mb4_roman_ci | utf8mb4 | 239 | Yes | 8 | |
utf8mb4_persian_ci | utf8mb4 | 240 | Yes | 8 | |
utf8mb4_esperanto_ci | utf8mb4 | 241 | Yes | 8 | |
utf8mb4_hungarian_ci | utf8mb4 | 242 | Yes | 8 | |
utf8mb4_sinhala_ci | utf8mb4 | 243 | Yes | 8 | |
utf8mb4_german2_ci | utf8mb4 | 244 | Yes | 8 | |
utf8mb4_croatian_ci | utf8mb4 | 245 | Yes | 8 | |
utf8mb4_unicode_520_ci | utf8mb4 | 246 | Yes | 8 | |
utf8mb4_vietnamese_ci | utf8mb4 | 247 | Yes | 8 |
一、sql_safe_updates 限制修改及删除语句
sql_safe_updates这个MySQL自带的参数就可以解决我们的问题,并且该参数是可以在线变更。当该参数开启的情况下,你必须要在UPDATE和DELETE语句后携带WHERE条件,否则就会报出ERROR!
- sql_safe_updates=0,即未开启
- sql_safe_updates=1,表示开启
数值和关键字都可以使用:0=OFF,1=ON。
5.0,5.1都是session级别的,5.6及以后是global&session级别。
低版本的数据库只能在创建session连接时设置带上set sql_safe_updates=on; 高版本的数据库可以直接
SET GLOBAL sql_safe_updates = 1
,设置完成后需要重新连接生效。 重启后,即使全局设置的限制也会失效,一般恢复到默认无限制,需要重新设置。
1. 级别
当前连接级别
set sql_safe_updates=1;
全局级别
SET GLOBAL sql_safe_updates = 1;
注意⚠️
- 设置之后要求重新连接才可生效
- 数据库重启后要及时执行否则恢复默认的限制状态
2. 查看当前限制开启状态
show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | ON |
+------------------+-------+
ON表示开启。
3. 异常信息
当sql_safe_updates=1时,即开启安全更新执行不加WHERE条件的SQL时报错信息如下
mysql> UPDATE safe_table SET futility_row=0;
1175 - You are using safe update mode and you tried to update a table
without a WHERE that uses a KEY column.
4. 测试数据表
CREATE TABLE `iot`.`safe_table` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`futility_row` int(11) NOT NULL DEFAULT 0 COMMENT '无用的列,安全更新使用',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci
COMMENT = '安全更新、删除测试表' ROW_FORMAT = Dynamic;
5. 条件限制
⚠️:key表示索引,const表示常量。YES可以执行,NO不能执行。
操作条件 | delete | update |
---|---|---|
no where | NO | NO |
where key | YES | YES |
where no key | NO | NO |
limit | NO | YES |
where no key+limit | YES | YES |
where key+limit | YES | YES |
where const | NO | NO |
where const+limit | NO | YES |
常用命令
查看当前连接的IP地址列表及连接个数
SELECT SUBSTRING_INDEX( HOST, ':', 1 ) AS ip, count(*)
FROM information_schema.PROCESSLIST
GROUP BY ip;
DESC `user`;-- 表信息,DESCRIBE的缩写
SHOW CREATE TABLE `user`;-- 表创建语句
SHOW CREATE DATABASE iot;-- 数据库简单信息,创建语句
SHOW TABLE STATUS FROM iot; /* 显示当前使用或者指定的database中的每个表的信息。
信息包括表类型和表的最新更新时间*/
SHOW TABLES FROM iot;-- 显示数据库所有表的名称,没有from默认数据库
SHOW DATABASES [LIKE "i%"];-- 显示mysql中所有数据库的名称,模糊查询i开头的
SHOW PROCESSLIST;/* 显示系统中正在运行的所有进程,也就是当前正在执行的查询。
大多数用户可以查看他们自己的进程,但是如果他们拥有process权限,就可以查看所有人的进程,包括密码。*/
SHOW COLUMNS FROM `user` FROM iot;-- 显示表中列名称
SHOW GRANTS FOR root@localhost;-- 显示一个用户的权限,显示结果类似于grant命令
SHOW INDEX FROM `user`;-- 显示表的索引
SHOW STATUS; -- 显示一些系统特定资源的信息300+,例如,正在运行的线程数量
SHOW VARIABLES;-- 显示系统变量的名称和值
show privileges;-- 显示服务器所支持的不同权限
SHOW ENGINES;-- 显示安装以后可用的存储引擎和默认引擎。
SHOW ERRORS;-- 只显示最后一个执行语句所产生的错误
SHOW ENGINE INNODB STATUS;-- 查看到死锁信息
SHOW INNODB STATUS;-- 显示innoDB存储引擎的状态
SHOW LOGS;-- 显示最后一个执行的语句所产生的错误、警告和通知
查询数据表的表结构
SELECT
column_name columnName,
data_type dataType,
column_comment columnComment,
column_key columnKey,
extra
FROM
information_schema.COLUMNS
WHERE
table_name = "sys_user"
AND table_schema = ( SELECT DATABASE ( ) )
ORDER BY
ordinal_position
列名、类型、备注、键类型、自增