MySql常用sql

数字转换成时间

FROM_UNIXTIME(s.time),DATE_FORMAT(FROM_UNIXTIME(s.time), '%Y-%m-%d %H:%i:%s')

字符集

查看mysql支持字符集

show character set;

选择database查看字符集

show variables like 'character_set%';

更改默认字符集

alter database zzp default character set utf8;

查看建表语句（包含字符集）

show create table tab_city;

查看索引

show index from tab_city;

转换字符串/数字

方法1：使用CAST函数

SELECT CAST(number AS CHAR) AS str_number FROM table_name;
SELECT CAST(column_name AS unsigned) FROM table_name;

方法2：使用CONVERT函数

SELECT CONVERT(number, CHAR) AS str_number FROM table_name;
SELECT CONVERT(column_name, SIGNED INTEGER) FROM table_name;

方法3：使用CONCAT函数

SELECT CONCAT('', number) AS str_number FROM table_name;

拼接字段

CONCAT_WS('|',COALESCE(b.username, ''),b.serv_type_id)

为空

ifnull(max(devicetype_id),0)
COALESCE(b.username, '')

修改字段

ALTER TABLE tab_gw_device MODIFY x_com_passwd_old varchar(50) default 'nE7jA%5m' not null;
ALTER TABLE errorlogin ALTER COLUMN time SET DEFAULT 0;

事物/撤回

start transaction;
insert into XXX
commit/rollback

索引

show index from tab_name
DROP index I_SPEEDRESULT_TASK_PPPOE ON tab_name;
ALTER TABLE tab_batchhttp_result 
ADD INDEX idx_requesttime (requesttime),
ALGORITHM=INPLACE, 
LOCK=NONE; -- 基本不会锁表

检查索引

explain select device_id from stb_tab_gw_device where device_id='102';

使用索引：

type：

const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可

eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高. 关键字:连接字段 主键或者唯一性索引

ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询. 关键字:非唯一性索引

range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.

index: 表示全索引扫描(full index scan), 关键字:查询字段和条件都是索引本身

all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

  ：具体的索引名称，PRIMARY代表是主键。

Extra：包含Usering index则代表使用索引。

未使用索引：

删除记录（LIMIT）

1.加索引
2.加索引的时候避免锁表，指定算法
ALTER TABLE tab_batchhttp_result 
ADD INDEX idx_requesttime (requesttime),
ALGORITHM=INPLACE, 
LOCK=NONE;

3.删除时根据索引删除，加limit条件
DELETE FROM tab_batchhttp_result WHERE requesttime < 1718851403 LIMIT 100000;

锁表

SHOW OPEN TABLES WHERE `Table` = 'tab_name' AND `Database` = 'fjm';

分析表

OPTIMIZE table tab_name;

最大连接数

show variables like "max_connections"; #显示最大连接数show processlist; #显示当前连接数
set global max_connections=1000; #设置最大连接数1000 　
show variables like "max_connections"; #显示最大连接数

找到mysql安装目录下的my.ini，修改max_connections=1000，重启数据库就永久生效了

max_connections=1000

 

事务执行情况

select trx_id,trx_state,trx_started,trx_query,trx_rows_locked,trx_rows_modified,trx_mysql_thread_id  FROM information_schema.innodb_trx ORDER BY trx_started ASC LIMIT 10;

事务执行

START TRANSACTION; -- 开始事务

-- 执行删除语句（这里以方案一为例）
DELETE d
FROM tab_batchhttp_task_dev d
INNER JOIN tab_batchhttp_task t ON d.task_id = t.task_id
WHERE t.add_time < 1732070603;

-- 再次查询确认，或者检查受影响的行数
-- 如果确认删除正确，则提交
COMMIT;

-- 如果发现删错了，则回滚，数据会恢复
-- ROLLBACK;