参考文献

数据库大小敏感问题

  • Oracle
    • 默认是大小写不敏感,表名,字段名等不区分大小写,小写字母会自动转换为大写字母;
    • 需要用小写字母时需要使用双引号或借助函数lower();
  • PostgreSQL
    • 默认是大小写不敏感,表名,字段名等不区分大小写,大写字母会自动转换为小写字母;
    • 需要用大写字母时需要使用双引号或借助函数upper();
  • SQLServer
    • 默认是大小写不敏感;
  • MySQL
    • 在Linux环境下数据库名,表名,列名,别名的大小写规则是这样的:
      • 数据库名与表名是严格区分大小写的;
      • 表的别名是严格区分大小的;
      • 列名与列的别名在所有情况下均是忽略大小写的;
      • 变量名也是严格区分大小写的;
    • 在Windows环境下都是不区分大小写的
  • 在不同操作系统中为了能是程序和数据库都能正常运行,最好的办法是在设计的时候都转为小写,但是如果在设计的时候已经规范大小写了,那么可以在MySQL配置文件中my.ini中的[mysqld]中增加一行low_case_table_names=1
    • 0: 区分大小写;
    • 1: 不区分大小写;

数据库编码和字符集引发的乱码问题

  • 乱码的本质原因是:数据编码和数据解码所使用的编码方式一致.

问题现象

  • MySQL版本8.0.25

    1
    2
    3
    4
    5
    6
    7
    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 8.0.25    |
    +-----------+
    1 row in set (0.01 sec)

  • MySQL字符集变量所设置的值

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    mysql> show variables like '%character%';
    +--------------------------+--------------------------------+
    | Variable_name            | Value                          |
    +--------------------------+--------------------------------+
    | character_set_client     | latin1                         |
    | character_set_connection | latin1                         |
    | character_set_database   | utf8mb4                        |
    | character_set_filesystem | binary                         |
    | character_set_results    | latin1                         |
    | character_set_server     | utf8mb4                        |
    | character_set_system     | utf8mb3                        |
    | character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
    +--------------------------+--------------------------------+
    8 rows in set (0.01 sec)

  • 程序中配置的MySQL的URL中所指定的字符编码为UTF-8

    1
    jdbc:mysql://$IP/xxxx?useUnicode=true&characterEncoding=utf8

  • 使用mysql -u $USER -p命令登录,在数据表中插入中文字符并使用查询语句查询,显示正常.但是使用程序来读取的时候出现了中文乱码.

    1
    2
    3
    4
    5
    6
    7
    mysql> select * from unique_test;
    +----+--------------+
    | id | content      |
    +----+--------------+
    |  1 | 测试编码测试编码测试编码测试编码测试编码测试编码|
    +----+--------------+
    1 row in set (0.00 sec)

  • 使用mysql -u root --default-character-set=utf8mb4 -p登录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    root@bef53fced63c:/# mysql -u root --default-character-set=utf8mb4 -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 15
    Server version: 8.0.22 MySQL Community Server - GPL

    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed
    mysql> select * from unique_test;
    +----+-----------------------------+
    | id | content                     |
    +----+-----------------------------+
    |  1 | æµ‹è¯•ç¼–ç                 |
    +----+-----------------------------+
    1 row in set (0.00 sec)
    img

问题原因

  • 因为mysql服务配置项character_set_client 和character_set_connection都为latin1编码方式,使用mysql -u $USER -p并为指定字符集故而登录成功后使用系统默认的字符集(即latin1),而程序使用到的编码为UTF-8,故而编码和解码所使用的方式不一致,最终导致乱码的出现.

处理方法

  • 治标
    • 在登录mysql的使用指定字符集mysql -u $USER --default-character-set=utf8mb4 -p
  • 治本
    • 修改mysql服务的客户端编码和连接编码改为utf8mb4

MySQL datetime类型长度问题

  • 示例

    1
    2
    // create_time字段 类型为datetime 长度为6
    created_time  datetime 6

  • 以2022-03-14 18:32:33.000000为例datetime长度为6,6为这个时间格式"."后面数字的长度.

  • 若不指定datetime的长度,则数据格式为2022-03-14 18:32:33

MySQL order by 排序使用的字段有重复值导致排序随机的情况

问题描述

  • 有一张数据表,其中有部分数据支付时间是一样的,通过SQL对创建时间排序以及分页(pageNo,pageSize) 发现调整pageSize的值 如5,10,400第一条的数据会变化,即数据的顺序不固定

官方说明

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

如果ORDER BY列中的多行具有相同的值,服务器可以自由地以任何顺序返回这些行,而且根据总体执行计划的不同,返回顺序也可能不同。换句话说,与无序列相比,这些行的排序顺序是不确定的。

解决方案

官方给的解决方案

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this:

如果确保使用和不使用“LIMIT”的行顺序很重要,那么在“order BY”子句中包括额外的列,以使顺序具有确定性。例如,如果’ id ‘值是唯一的,你可以让一个给定的’ category ‘值的行出现在’ id '顺序,排序如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+
  • 即加上额外的列,保证顺序确定.

MySQL隐式转换问题

MySQL源码环境搭建遇到的问题

环境说明

  • 在Clion中进行编译

  • 基于macOS 13.0.1

    1
    2
    3
    4
    > sw_vers
    ProductName:		macOS
    ProductVersion:		13.0.1
    BuildVersion:		22A400

  • MySQL版本为MySQL 8.0.27

  • cmake指令为

    1
    2
    3
    4
    5
    6
    cmake \
    -DWITH_DEBUG=1 \
    -DDOWNLOAD_BOOST=1 \
    -DDOWNLOAD_BOOST_TIMEOUT=60000 \
    -DWITH_BOOST=/Users/holelin/Component/boost/boost_1_73_0 \
    -DWITH_SSL=/opt/homebrew/Cellar/openssl@1.1/1.1.1s

报错信息

1
2
3
4
5
CMake Error at cmake/package_name.cmake:92 (MESSAGE): 
	Could not run sw_vers 
Call Stack (most recent call first): cmake/package_name.cmake:150
	(GET_PACKAGE_FILE_NAME) 
	CMakeLists.txt:754 (INCLUDE)

原因分析

  • 根据报错信息查看cmake/package_name.cmake:92文件报错行,发现当前文件有个IF(NOT CMAKE_MATCH_1 OR NOT CMAKE_MATCH_2)判断,问题原因应该是这个判断满足了报出了这个Could not run sw_vers错误信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# CMAKE_SYSTEM_PROCESSOR seems to based on 'uname -r'
# CMAKE_SYSTEM_VERSION cannot be trusted for version information:

# CMAKE_SYSTEM_VERSION 19.2.0
# sw_vers
# ProductName:    Mac OS X
# ProductVersion: 10.15.2
# BuildVersion:   19C57

# CMAKE_SYSTEM_VERSION  20.2.0
# sw_vers
# ProductName:    macOS
# ProductVersion: 11.1
# BuildVersion:   20C69

EXECUTE_PROCESS(COMMAND sw_vers
  OUTPUT_VARIABLE SW_VERS_OUTPUT
  OUTPUT_STRIP_TRAILING_WHITESPACE
  )
STRING(REPLACE "\n" ";" SW_VERS_OUTPUT_LIST "${SW_VERS_OUTPUT}")
LIST(GET SW_VERS_OUTPUT_LIST 0 SW_VERS_PRODUCTNAME)
LIST(GET SW_VERS_OUTPUT_LIST 1 SW_VERS_PRODUCTVERSION)

STRING(REGEX MATCH
  "ProductVersion:[\n\t ]*([0-9]+)\\.([0-9]+)" UNUSED ${SW_VERS_PRODUCTVERSION})
IF(NOT CMAKE_MATCH_1 OR NOT CMAKE_MATCH_2)
  MESSAGE(FATAL_ERROR "Could not run sw_vers")
ENDIF()

解决办法

  • 方法一: 将判断逻辑注释掉,忽略这个检查(这个办法经过测试是可行的,但是还是建议根据官方提供的补丁来解决,即方法二)

    1
    2
    3
    4
    5
          STRING(REGEX MATCH
            "ProductVersion:[\n\t ]*([0-9]+)\\.([0-9]+)" UNUSED ${SW_VERS_PRODUCTVERSION})
    #      IF(NOT CMAKE_MATCH_1 OR NOT CMAKE_MATCH_2)
    #        MESSAGE(FATAL_ERROR "Could not run sw_vers")
    #      ENDIF()

  • 方法二:采用官方认可的补丁 https://bugs.mysql.com/bug.php?id=105464

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    iff --git a/cmake/package_name.cmake b/cmake/package_name.cmake 
    index 058b0bcbd8c..7983475221c 100644 
    --- a/cmake/package_name.cmake 
    +++ b/cmake/package_name.cmake 
    @ @ -88,7 +88,7 @@ MACRO(GET_PACKAGE_FILE_NAME Var) 

           STRING(REGEX MATCH 
             "ProductVersion:[\n\t ]*([0-9]+)\\.([0-9]+)"未使用 ${SW_VERS_PRODUCTVERSION}) 
    - IF(NOT CMAKE_MATCH_1 OR NOT CMAKE_MATCH_2) 
    + IF(NOT DEFINED CMAKE_MATCH_1 OR NOT DEFINED CMAKE_MATCH_2) 
             MESSAGE(FATAL_ERROR "Could not run sw_vers") 
           ENDIF()

MySQL Load Data Local Infile Error

1
2
ERROR 3950 (42000): Loading local data is disabled; this must be
enabled on both the client and server side

解决办法

  • 开启服务器端加载文件配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | local_infile  | OFF   |
    +---------------+-------+
    1 row in set (0.01 sec)

    mysql> SET GLOBAL local_infile = on;
    Query OK, 0 rows affected (0.00 sec)

  • 开启客户端加载文件配置

    1
    mysql --local-infile=1 -u root -p