mysql 常见问题

1.

1.  null 不做值的比较，相等 

#  说明 field = value   , field != value  都会过滤掉 field 值 为 null 的数据
select * from girls   ;

# +--+----+------+---+--------+----------+-------------------+-------------+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime        |num          |
# +--+----+------+---+--------+----------+-------------------+-------------+
# |1 |慕容a |1     |W  |        |1         |2020-07-23 15:13:48|20200723_0002|
# |2 |慕容b |1     |W  |        |NULL      |2020-07-23 14:13:48|20200723_0001|
# |3 |慕容c |1     |W  |        |NULL      |2020-07-23 15:13:48|20200723_0003|
# |4 |慕容d |1     |W  |        |          |2020-07-23 16:13:48|20200723_0004|
# +--+----+------+---+--------+----------+-------------------+-------------+

#
select * from girls where is_deleted= 0 ;
# +--+----+------+---+--------+----------+-------------------+-------------+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime        |num          |
# +--+----+------+---+--------+----------+-------------------+-------------+
# |4 |慕容d |1     |W  |        |          |2020-07-23 16:13:48|20200723_0004|
# +--+----+------+---+--------+----------+-------------------+-------------+

select * from girls where is_deleted= '0' ;

# +--+----+------+---+--------+----------+-----------+---+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime|num|
# +--+----+------+---+--------+----------+-----------+---+

select * from girls where is_deleted= 1 ;

# +--+----+------+---+--------+----------+-------------------+-------------+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime        |num          |
# +--+----+------+---+--------+----------+-------------------+-------------+
# |1 |慕容a |1     |W  |        |1         |2020-07-23 15:13:48|20200723_0002|
# +--+----+------+---+--------+----------+-------------------+-------------+


select * from girls where is_deleted != 1 ;

# +--+----+------+---+--------+----------+-------------------+-------------+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime        |num          |
# +--+----+------+---+--------+----------+-------------------+-------------+
# |4 |慕容d |1     |W  |        |          |2020-07-23 16:13:48|20200723_0004|
# +--+----+------+---+--------+----------+-------------------+-------------+


# 查的是 除 null 以外 的 数据
select * from girls where is_deleted != 2 ;

# +--+----+------+---+--------+----------+-------------------+-------------+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime        |num          |
# +--+----+------+---+--------+----------+-------------------+-------------+
# |1 |慕容a |1     |W  |        |1         |2020-07-23 15:13:48|20200723_0002|
# |4 |慕容d |1     |W  |        |          |2020-07-23 16:13:48|20200723_0004|
# +--+----+------+---+--------+----------+-------------------+-------------+

1. 查看表字符集，字符集排序和修改表字符集字符集排序

# 查看表的字符集
SHOW CREATE TABLE mytable;


# 修改字符集 和字符集 排序
alter table mytableCONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

#  上面有语法问题的话就用下面的
alter table order_test  convert to character set utf8mb4  COLLATE utf8mb4_general_ci ;

# 修改字段的字符集：

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

2. 或者建表的同时指定字符集和字符集排序

create table x_UnBusinessPaymentTime
(
    *********
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='测试'

1.

修改数据库字符集

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

只是修改表的默认字符集
　　
ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

修改字段的字符集

ALTER TABLE tbl_name CHANGE title title VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查看数据库编码

SHOW CREATE DATABASE db_name;

查看表编码

SHOW CREATE TABLE tbl_name;

查看字段编码

SHOW FULL COLUMNS FROM tbl_name;

1.  因为utf8并不支持拼音的排序

按照汉字排序 两个方法（order by  sql 中 转 成gbk ； 或者将 表字段的编码改为 gbk）

链接：mysql按照汉字拼音排序_yjclsx的博客-CSDN博客_mysql按照中文拼音排序

1. 如果用其他的 排序规则

CREATE TABLE `order_test` (
  `CwztGUID` varchar(100) DEFAULT NULL,
  `CwztName` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

sql 中修改编码集排序规则

SELECT order_test.Name AS Name , order_test.GUID AS GUID
FROM order_test
ORDER BY convert(order_test.Name using gbk) desc ;

or

SELECT order_test.name AS name , order_test.guid AS guid 
FROM order_test
ORDER BY convert(order_test.name using gbk)  COLLATE gbk_chinese_ci desc ;

修改字段的编码集

SHOW CREATE TABLE order_test;


# ALTER TABLE order_test CHANGE CwztName CwztName CHARACTER  SET character_name [COLLATE ...];
# ALTER TABLE order_test CHANGE CwztName CwztName VARCHAR(100) CHARACTER SET gbk COLLATE gbk_chinese_ci ;

ALTER TABLE order_test CHANGE CwztName CwztName VARCHAR(100) CHARACTER SET gbk COLLATE gbk_chinese_ci ;

1. 日期比较（前段传个字符串，既可以和库里的 datgetime 类型比较，也可以 和 库里的  varchar2 类型的比价）

# create_time_str 是字符串类型
select create_time_str from boys  where create_time_str >= '2021-05-22';
# created_time 是datetime 类型
select create_time_str from boys  where created_time >= '2021-05-22';
# 脚本如下
create table boys
(
    id              int                     not null
        primary key,
    name            varchar(20)             null,
    sex             varchar(20) default '男' null comment '默认男',
    age             varchar(20)             null,
    address         int                     null,
    created_time    datetime                null,
    enum            int                     null,
    create_time_str varchar(42)             null
)
    comment '男生表';