mysql SQL

🧙 Questions

• 隐藏部分的手机号码

☄️Ideas

连接mysql

docker exec -it isxcode-mysql bash
mysql -h isxcode -u ispong -pispong2021 -P 30102

show databases;
use data_assets;
drop table ispong_table;
create table user1
(
    id       varchar(200),
    username varchar(200)
);
create table user2
(
    id       varchar(200),
    age      int      
);

CREATE VIEW users AS select user1.id,username,age from user1 left join user2 on user1.id = user2.id 

INSERT INTO data_assets.ispong_table (username, age, birth) VALUES ('lisi', 14, '2021-09-27 14:48:14')

隐藏部分的手机号码

select insert(PHONE, 5, 11, '********')
from USERS
limit 1;

string 转 date

select str_to_date('2020-12-01 12:12:12','%Y-%m-%d %H:%i:%s') from dual

date 转 string

select date_format(now(),'%Y-%m-%d %H:%i:%s') from dual

创建库

···
CREATE DATABASE ispong_demo DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
CREATE DATABASE ispong_assets DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
···

查询表中各字段的字符集

-- SHOW FULL COLUMNS FROM COLUMNS_V2
SHOW FULL COLUMNS FROM ${tableName}

给表加索引

CREATE INDEX idx_${table_name}_${column_id} ON TABLE1 (COLUMN_ID1);

一个月前的时间

select DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
select DATE_SUB(CURDATE(), INTERVAL 1 WEEK);

执行sql脚本

docker cp admin.sql test-link-mysql:/tmp/
docker exec -it test-link-mysql bash
mysql -h localhost -u root -p'123' -P 3306 < /tmp/admin.sql

mysql执行中文乱码

BEGIN;
SET NAMES utf8;

mysql排查表大小

SELECT 
    table_schema AS '数据库名',
    table_name AS '表名',
    ROUND(data_length/1024/1024, 2) AS '数据大小(MB)',
    ROUND(index_length/1024/1024, 2) AS '索引大小(MB)',
    ROUND((data_length + index_length)/1024/1024, 2) AS '总大小(MB)',
    table_rows AS '行数'
FROM 
    information_schema.TABLES
WHERE 
    table_schema = 'dehoop'
ORDER BY 
    (data_length + index_length) DESC;

ALTER TABLE das_api_logs RENAME TO das_api_logs_2025_07_19; 
create table das_api_logs like das_api_logs_2025_07_19;
insert into execution_flows select * from execution_flows_2025_07_19 where CREATION_DATE > DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
insert into das_api_logs select * from das_api_logs_2025_07_19 where CREATION_DATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY);

🔗 Links