01-sql

1. In

1. In仍然会使用索引

EXPLAIN select m.id,
       m.app_id,
       m.menu_name,
       m.menu_code,
       m.parent_id,
       m.url,
       m.type,
       '01' as funcCategory
FROM menu m
         INNER JOIN role_menu rm
                    ON rm.MENU_ID = m.ID
         INNER JOIN role r
                    ON r.ID = rm.ROLE_ID
WHERE m.APP_ID = '8101657204559'
  and r.ROLE_CODE in ('OoxxLogin')
  and m.TYPE = 1
  and m.IS_SHOW = 1
  and m.LOGIN_FROM = 1
order by m.parent_id, m.sort_no, m.create_time;

2. syntax

mysql实现merge into的方法

-- merge语法：
MERGE INTO [target-table] A USING [source-table sql] B
ON([conditional expression] and [...]...)
WHEN MATCHED THEN
 [UPDATE sql]
WHEN NOT MATCHED THEN
[INSERT sql]

3. Sql

1. varchar大小写敏感

2. insert from select

# 1. 所有字段
INSERT INTO user_role
select uuid()                as ID,
       '32487378776789623'  as APP_ID,
       ID                    as USER_ID,
       ''                    as ROLE_ID,
       1                     as STATUS,
       null                  as EXPIRE_TIME,
       'songsong'            as CREATE_BY,
       '2021-06-24 14:31:32' as CREATE_TIME,
       'songsong'            as LAST_UPDATE_BY,
       '2021-06-24 14:31:32' as LAST_UPDATE_TIME
from user
where CREATE_TIME = '2021-06-24 14:31:32';

# 2. 部分字段

3. uuid()

uuid() 可以直接生成数据库唯一id

4. id_path name_path妙用

# 更新DEPT_NAME_PATH
update dept
set DEPT_NAME_PATH = replace(DEPT_NAME_PATH, 'ooxx平台/邯郸4', 'ooxx平台/邯郸3')
WHERE APP_ID = '1471444606'
  and DEPT_ID_PATH like concat('1471444606/817125998922436608', '%');

# 查询按钮下的菜单
select m.ID
from menu m
         left join menu m2 on m.MENU_ID_PATH like concat(m2.MENU_ID_PATH, '%')
where m2.TYPE = 2
  and m.TYPE = 1;

# 更新按钮下的菜单为按钮
update menu
set t_TYPE = 2
where id in (select id
             from (select m.ID
                   from menu m
                            left join menu m2 on m.MENU_ID_PATH like concat(m2.MENU_ID_PATH, '%')
                   where m2.t_TYPE = 2
                     and m.t_TYPE = 1) T);

1. menuTree移植到另一个Node下

# PARENT_ID变更
update menu
set PARENT_ID = '704998994161569792',
    APP_ID    = '1471444606'
where PARENT_ID = '654061976451944448';

5. 导出查询数据

1. 导出指定格式数据

MySQL 导出数据

select login_name, user_code as authkey, CREATE_TIME, STATUS
from t_user
where create_time = '2021-06-24 14:31:32'
into outfile '/users/list/documents/user2.sql'
    fields terminated by ', '
    optionally enclosed by '\''
    lines terminated by '\n';

2. 拼接sql

/**
 * @author: lisongtao
 * @description: ThreadPoolExecutorTest
 * @create: 2021/6/27 7:39 PM
 */
static class SelToInsSql {
    public static void main(String[] args) throws IOException {
        InputStream is = new FileInputStream("/Users/list/Documents/user2.sql");
        OutputStream out = new FileOutputStream("/Users/list/Documents/user2_copy.sql");
        String insPrefix = "INSERT INTO user(`LOGIN_NAME`, `AUTH_KEY`) VALUES (";

        BufferedReader reader = new BufferedReader(new InputStreamReader(is));
        BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(out));
        String one;
        StringBuilder sb = new StringBuilder();
        while (true) {
            one = reader.readLine();
            if (one != null) {
                sb.append(insPrefix)
                        .append(one)
                        .append(");");
                writer.write(sb.toString());
                writer.newLine();
                sb.delete(0, sb.length());
            } else {
                break;
            }
        }
        reader.close();
        writer.close();
    }
}

6. date

• mysql数据库datetime只能精确到秒，java Date date = new date() 精确到毫秒，date插入的数据，date会查询不到。用hutool的 Date date = DateUtil.dateSecond(); 来创建时间对象
• 2022-02-15 14:37:32.0(Timestamp)

4. 常用sql语法

1. date_format()

private String groupTypeDateFormat(String groupType) {
    if ("hour".equals(groupType)) {
        return "%Y-%m-%d %h";
    } else if ("day".equals(groupType)) {
        return "%Y-%m-%d";
    } else if ("month".equals(groupType)) {
        return "%Y-%m";
    }
    return null;
}

2. concat()

ROLE_CODE like CONCAT('%', #{roleCode}, '%')

3. foreach

<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
    #{item.id}
</foreach>

4. Base_Column_List

<sql id="Base_Column_List">
    ID, APP_CODE, APP_NAME, APP_URL, REMARK, STATUS, CREATE_BY, CREATE_TIME, LAST_UPDATE_BY,
    LAST_UPDATE_TIME, DEL_FLAG
</sql>

<include refid="Base_Column_List"/>

5. tinyint(4)

• tinyint(1)和tinyint(4)的区别和用法
• Java 基本数据类型
• tinyint 默认 tinyint(4) 带符号的范围是-128到127。无符号的范围是0到255。

java 的 Byte 类型 (-128~127)

6. upd case ifnull isnull

update t_user
set CHILD_NUM = case
                    when (ifnull(CHILD_NUM, 0) + '') > 0 then (ifnull(CHILD_NUM, 0) + '')
                    else 0 end
where ID = ''

5. common_sql

1. 当日登录过的人数

select count(*)
from login_log
where login_name like concat(date_format(now(), '%Y-%m-%d'), '%');

2. 查询表某字段的重复数据

# 查询表重复数据
select login_name from t_user group by login_name having count(login_name) > 1

# 查询表重复数据
select *
from t_user
where login_name in (select login_name from t_user group by login_name having count(login_name) > 1);

# 删除重复的权限
delete ur
from user_role ur,
     (select id
      from t_user
      where login_name in (select login_name from t_user group by login_name having count(login_name) > 1)) t
where t.id = ur.user_id;

# 删除一张表中的重复数据（You can't specify target table 'user' for update in FROM clause，不能在同一表中查询的数据作为同一表的更新数据）所以要包一层select
delete
from t_user
where login_name in (
    select login_name
    from (select login_name
          from t_user
          group by login_name
          having count(login_name) > 1) a
);

# 删除一张表中重复数据，保留一条
delete
from t_user
where login_name in (
    select login_name
    from (select login_name
          from t_user
          group by login_name
          having count(login_name) > 1) a
)
  and id not in (select id
                 from (select min(ID) as id
                       from t_user
                       group by login_name
                       having count(login_name) > 1) b);

3. mybatis怎样直接执行sql

@PostMapping("/executeSql")
public List<Map<String, Object>> executeSql(@RequestBody String executeSql) {
    return mysqlMapper.executeSql(executeSql);
}

List<Map<String, Object>> executeSql(@Param("mysqlSel") String mysqlSel);

<select id="executeSql" resultType="java.util.Map">
    ${mysqlSel}
</select>

4. 多表关联删除

delete ur
from user_role ur
         inner join t_user u on ur.USER_ID = u.ID
where u.USER_TYPE = '30';

delete ur
from user_role ur,
     t_user u
where ur.USER_ID = u.ID
  and u.USER_TYPE = '30';

5. 多表关联更新

update t_user u inner join dept d on u.OOXX_ID = d.DEPT_CODE
set u.DEPT_ID        = d.ID,
    u.DEPT_NAME      = d.DEPT_NAME,
    u.DEPT_NAME_PATH = d.DEPT_NAME_PATH
where u.CREATE_TIME = '2021-06-24 14:31:32';

update t_user u, dept d
set u.DEPT_ID        = d.ID,
    u.DEPT_NAME      = d.DEPT_NAME,
    u.DEPT_NAME_PATH = d.DEPT_NAME_PATH
where u.OOXX_ID = d.DEPT_CODE
  and u.CREATE_TIME = '2021-06-24 14:31:32';

6. 正则查询

MySQL之正则表达式

select *
from user
where LOGIN_NAME regexp '^1[3456789][0-9]{9}$';

• 不能用 ^1[3456789]\d{9}$，只能用最原生的

7. 日期查询

协调世界时

UTC (Universal Time Coordinated)

2019-11-15T03:15:21 查询sql时候要 + 8h

中国大陆、中国香港、中国澳门、中国台湾的时间与UTC的时差均为+8，也就是UTC+8

# 等于
select ID, LOGIN_NAME, CREATE_TIME
from t_user
where CREATE_TIME = '2019-11-15 11:15:21';

# 大于
select ID, LOGIN_NAME, CREATE_TIME
from t_user
where CREATE_TIME > '2021-08-20 10:16:33';

# 小于
select ID, LOGIN_NAME, CREATE_TIME
from t_user
where CREATE_TIME < '2019-09-24 18:56:48';

# 区间
select ID, LOGIN_NAME, CREATE_TIME
from t_user
where CREATE_TIME between '2019-11-15 11:15:21' and '2019-11-15 11:15:33';

1. DATE_SUB()

• date_sub
• Mysql时间操作（当天，昨天，7天，30天，半年，全年，季度）

# 当前时间的6个月前
select date_sub(now(), interval 6 month);

# 清除6个月前的日志
delete from login_log where LOGIN_TIME < date_sub(now(), interval 6 month);

# 清除6个月前的user_token
delete ut from user_token ut where CREATE_TIME < date_sub(now(), interval 3 month);

8. 统计数据库每张表大小

# bj库表数据量总结
SELECT table_name, table_rows
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'bj'
ORDER BY table_rows DESC;

6. user_create_batch

1. java

static class CreateUserBatch {
    public static void main(String[] args) throws IOException {
        long l = System.currentTimeMillis();

        File toFile = new File("/Users/list/Documents/user.sql");
        FileOutputStream os = new FileOutputStream(toFile);
        BufferedOutputStream bos = new BufferedOutputStream(os);

        ExcelReader excelReader = ExcelUtil.getReader("/Users/list/Documents/ox.xlsx", 0);
        Snowflake snowflake = IdUtil.getSnowflake(1, 1);
        // yyyy-MM-dd HH:mm:ss
        DateTime dateTime = DateUtil.parse("2021-08-03 11:11:11", DatePattern.NORM_DATETIME_PATTERN);
        List<User> users = excelReader.readAll(User.class);
        StringBuilder sb = new StringBuilder();
        for (User one : users) {
            sb.delete(0, sb.length());
            one.setId(snowflake.nextIdStr());
            one.setAppId("1471444606");
            one.setAuthKey(CreateUserBatch.sm3(one.getLoginName(), one.getAuthKey()));
            one.setUserType(Byte.parseByte(one.getType()));
            one.setCreateBy("songsong");
            one.setCreateTime(dateTime);
            one.setLastUpdateBy("songsong");
            one.setLastUpdateTime(dateTime);
            String prefix = "insert into user (ID, APP_ID, LOGIN_NAME, AUTH_KEY, USER_TYPE, CREATE_BY, CREATE_TIME, LAST_UPDATE_BY, LAST_UPDATE_TIME) values (";
            Map<String, Object> map = BeanUtil.beanToMap(one, false, true);
            String join = CollUtil.join(map.values(), ", ", "'", "'");
            sb.append(prefix).append(join).append(");\n");

            bos.write(sb.toString().getBytes());
        }
        bos.close();
        System.out.println(System.currentTimeMillis() - l);
    }

    private static String sm3(String loginName, String authKey) {
        String originSm3 = loginName + authKey;
        SM3 sm3 = new SM3();
        return sm3.SM3Digest(originSm3);
    }
}

2. dept

update t_user u inner join dept d on u.OOXX_ID = d.DEPT_CODE
set u.DEPT_ID        = d.ID,
    u.DEPT_NAME      = d.DEPT_NAME,
    u.DEPT_NAME_PATH = d.DEPT_NAME_PATH
where u.CREATE_TIME = '2021-08-03 11:11:11';

3. user_role

INSERT INTO user_role
select uuid()               as ID,
       u.ID                 as USER_ID,
       r.ID                 as ROLE_ID,
       1                    as STATUS,
       null                 as EXPIRE_TIME,
       'songsong'           as CREATE_BY,
       u.CREATE_TIME        as CREATE_TIME,
       'songsong'           as LAST_UPDATE_BY,
       u.CREATE_TIME        as LAST_UPDATE_TIME
from t_user u
         inner join role r on r.ROLE_CODE = concat('autoRoleCode_', u.TYPE)
where u.CREATE_TIME = '2021-08-03 11:11:11';

7. Special character

1. \

• 两个 \ 相当于一个 \

select * from t_user where LOGIN_NAME like '%\\%';

2. '

• 两个 '' 相当于一个 '

select * from login_log where LOGIN_NAME like '%''%';

3. \u0000

select * from login_log where LOGIN_NAME like '%\0%';

8. lock

1205-Lock wait timeout exceeded; try restarting transaction

MySQL出现错误1205-Lock wait timeout exceeded; try restarting transaction

---

# 查看事物表，找出被锁线程的id
SELECT * FROM information_schema.INNODB_TRX;

# 根据id，kill掉被锁住的线程
kill 1129270;

# 问题sql，会导致锁表
update t_user u1 inner join t_user u2 on u2.OOXX_ID = u1.OOXX_ID
set u1.OX_ID  = u2.OX_ID,
    u1.OX_PWD = u2.OX_PWD,
    u1.O_STATUS  = u2.O_STATUS,
    u1.O_TYPE    = u2.O_TYPE
where u1.OX_ID is null
  and u2.OX_ID is not null
  and u1.ID <> u2.ID;

9. sql不区分大小写