连接池、连接时间

  1. <!-- 连接池最大数量 -->
  2. <property name="maxActive" value="60" />
  3. <property name="initialSize" value="10" />

MySQL 普通索引和唯一索引的区别

建表

ON UPDATE

  1. CREATE TABLE `tbl_menu` (
  2.     `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  3.     `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  4.     `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  5.         -- 其它字段...
  6. ) COMMENT = '菜单表';

SHOW COLLATION

MYSQL中的COLLATE是什么？
utf8mb4默认是utf8mb4_0900_ai_ci
uft8默认是utf8_general_ci

  • ci: CASE IGNORE
  • cs: CASE SENSITIVE ```sql SHOW COLLATION;

— 库级别设置 CREATE DATABASE DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

— 表级别设置 CREATE TABLE (

……

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

— 列级别 CREATE TABLE (

field1 VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’,

……

) ……

— 查询语句设置 SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1; SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;

  1. <a name="Y5ALo"></a>
  2. # 删除重复数据
  3. [javapoint.sql](https://www.yuque.com/attachments/yuque/0/2022/sql/436938/1642168141565-671cb972-9867-4ddf-b1e9-b6fe12053174.sql?_lake_card=%7B%22src%22%3A%22https%3A%2F%2Fwww.yuque.com%2Fattachments%2Fyuque%2F0%2F2022%2Fsql%2F436938%2F1642168141565-671cb972-9867-4ddf-b1e9-b6fe12053174.sql%22%2C%22name%22%3A%22javapoint.sql%22%2C%22size%22%3A3871%2C%22type%22%3A%22application%2Fsql%22%2C%22ext%22%3A%22sql%22%2C%22status%22%3A%22done%22%2C%22taskId%22%3A%22u6457db95-c602-48a5-b4f0-6574d3e2ba5%22%2C%22taskType%22%3A%22upload%22%2C%22id%22%3A%22u86df586f%22%2C%22card%22%3A%22file%22%7D)<br />GROUP BY + 联合索引（所有字段）<br />[MySQL 处理重复数据](https://www.runoob.com/mysql/mysql-handling-duplicates.html)
  4. ```java
  5. mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex);
  6. mysql> DROP TABLE person_tbl;
  7. mysql> ALTER TABLE tmp RENAME TO person_tbl;

Remove duplicate rows in MySQL——Stack Overflow
ADD UNIQUE INDEX + 联合索引（所有字段）

  1. ALTER IGNORE TABLE jobs
  2. ADD UNIQUE INDEX idx_name (site_id, title, company);

UNION

  1. -- Union
  2. -- 针对单个字段，可以这样去重
  3. SELECT customer_id FROM orders o 
  4. UNION
  5. SELECT customer_id FROM orders o;

or和in哪个效率高

MySQL的in语句和or哪个执行效率更高?——慕课网
image.png
image.png

DDL, DML, DQL, DCL

SQL语言：DDL、DML、DQL、DCL详解

update set from 语句用法

重命名数据库和表

如何修改MySQL数据库名称
脚本：

  1. #!/bin/bash
  2. mysql -uroot -p123456 -e 'create database if not exists new_db;'
  3. list_table=$(mysql -uroot -p123456 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='old_db'")
  5. for table in $list_table
  6. do
  7.     mysql -uroot -p123456 -e "rename table old_db.$table to new_db.$table"
  8. done
  • -e, —execute=name # 执行mysql的sql语句
  • -N, —skip-column-names # 不显示列信息

  • -s, —silent # 一行一行输出，中间有tab分隔

    While循环和Cursor游标

    Cursor in SQL
    cursor-in-sql-server

  • Implicit cursor & Explicit cursor

  • cursors are very bad in performance, so it should always use only when there is no option except the cursor

    Explain and Analyze

    javapoint mysql explain and analyze

    判断是否存在或获取第一行

    Sybase存储过程为例 ```sql — 1. SELECT @variable = ‘XXX’ FROM … DECLARE @has_dsv VARCHAR(1) BEGIN SELECT @has_dsv = ‘Y’ FROM tempdb..frtopt_order_frt_result WHERE spid = @@spid
    1.   AND ship_method = 'FO'
    2.   AND hide_flag IS NULL
    END SELECT @has_dsv;

— 2. SELECT 1 FROM … IF EXISTS ( SELECT 1 FROM tempdb..frtopt_order_frt_result WHERE spid = @@spid AND ship_method = ‘FO’ AND hide_flag IS NULL; ) BEGIN — … END

— 3. SET ROWCOUNT 1 DECLARE @exist_test VARCHAR(20) BEGIN SET ROWCOUNT 1 SELECT @exist_test = ship_method FROM tempdb..frtopt_order_frt_result SET ROWCOUNT 0 END SELECT @exist_test; ```

扩展字段

直接追加字段可能的影响：

I have to say the table territory is a very widely used one, if to add a new column, there would be some risk if the other program has ‘select * from territory’ issue, considering it’s an important table also be used in breport. So my suggestion is to add another table like sales_terr_mail, link it to territory table, it has the same effect and user has no inconvenience, but we could avoid the rish from IT side.

踩坑

  1. 表名不能含关键字，会报 ERROR 1064 (42000) ，要使用的话，需加``
  2. PostgreSql语句中的字符串必须用单引号，不能用双引号
  3. mysql启动异常，service mysql start/restart也异常

=> datadir配置错误，重装后把/var/lib/mysql移动到自定义datadir解决