MYSQL-По корням Правильно ли вставлено ваше выражение (🚀🚀🚀utf8mb4)?

MySQL

фон проблемы

Онлайн-мониторинг исключений обнаружил, что в проекте было исключение SQL, и было обнаружено, что выражение было вставлено ненормально.

image-20200601173013557

Среда проекта

线上MYSQL 版本：
	polarDB：5.6.16-log
测试MYSQL 版本：
	5.7.25-28-log
  5.7.25-28-log
druid: 
	1.1.10
druid-spring-boot-starter:
	1.1.10
mysql-connector-java:
	5.1.42

решение проблем

  • Подсознательно я прямо подумал, что в БД используется не тип utf8mb4, а прямо посмотрел на структуру таблицы БД и обнаружил, что таблица БД действительно utf8mb4
CREATE TABLE `t_course_resource` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程关联的资源id',
  `c_course_uid` char(32) NOT NULL DEFAULT '' CO',
  `c_uid` varchar(256) NOT NULL DEFAULT '' COMMENT '资源id',
  `c_name` varchar(64) NOT NULL DEFAULT '' COMMENT '资源名',
  `c_description` varchar(128) NOT NULL DEFAULT '' COMMENT '资源描述',
  `c_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `c_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  `c_is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '记录是否被删除',
  PRIMARY KEY (`id`),
  KEY `idx_course_uid` (`c_course_uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4220171 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='课程资源'
  • Чтобы убедиться, что на стороне базы данных нет проблем, отправьте конвейер напрямую, обновите sql через базу данных, отправьте строку выражения и убедитесь, что она может быть выполнена правильно.

image-20200601173143462

Вышеупомянутая двухэтапная проверка может временно исключить проблемы на стороне базы данных.

  • Тестовая среда и среда разработки попытались представить строки со смайликами в коде и обнаружили, что это возможно, Центр конфигурации сравнения и код были точно такими же, за исключением адреса базы данных. (В сети есть polarDB, а тест самодельный)

  • Глядя в документацию к базе данных Alibaba Cloud, я увидел такое предложение.Я обнаружил, что не рекомендуется настраивать characterEncoding.Потом я догадался о гадании и удалил конфигурацию.Я протестировал ее и обнаружил, что она все еще не работает.

    image-20200601174552744

  • Спросите у администратора базы данных, администратор базы данных рекомендует попробовать использовать тест конфигурации connectionInitSqls = setnames utf8mb4, я не изучал эту конфигурацию и не знаю, для чего она полезна, но боссы базы данных предложили это, поэтому я попробовал это действительно ароматный.

SQL-аутентификация

  • Установить анализ и проверку роли utf8mb4:

  • Тестовая таблица:

    CREATE TABLE `t_test_encoding` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  • 1. 连接数据数据库 ，执行 show global variables like 'character%'; 查看全局的字符集配置

    image-20200601194219169

  • 2. 执行 show variables like 'character%'; 查看当前会话的字符集配置
   执行插入表情操作 INSERT INTO `t_demo` ( `name`) VALUES ('测试表情🆚🏷❤️😌');发现报错。

    image-20200601194631780

    image-20200602110002199

  • 3. 执行 set names utf8mb4; 设置当前会话的字符集
4. 再次执行 show variables like 'character%'; 查看当前会话的字符集配置
	 再次执行插入表情操作 INSERT INTO `t_demo` ( `name`) VALUES ('测试表情🆚🏷❤️😌'); 插入OK

    image-20200601194811367

  • 5. 执行 show global variables like 'character%'; 查看全局的字符集配置

    image-20200601194853052

Суммировать:

1. 是否能够插入表情，不仅仅要求相关的字段是utf8mb4编码格式，utf8mb4仅仅只是前提
2. 除了要求字段的格式是utf8mb4，还要求当前的session会话连接的编码同时是utf8mb4
3. set name utf8mb4  只对当前会话有效，不影响其他连接和全局的配置

Анализ причин

  • Это конец проблемы? Конечно нет.Как обезьяна с преследованием,как это могло так закончиться?Мы должны тщательно изучить этот вопрос.Теперь есть две вещи,которые я не понимаю.

    • Для чего используется connectionInitSqls и почему достаточно его настроить?
    • Почему тестовая среда и среда разработки могут работать, но не онлайн? (Историческая проблема в мире программистов, почему я могу делать это локально, но зависаю на сервере [слезы] [слезы] [слезы])

  • Далее мы рассмотрим анализ этих двух проблем:

  • Вопрос 1:

    • connectionInitSqls - это уникальная конфигурация базы данных druid (см. официальную документацию, что этот параметр является sql, выполняемым при инициализации соединения), поэтому его легко понять. Когда мы выполняем set name utf8mb4, мы всегда используем формат кодировки utf8mb4 в текущий сеанс.

      image-20200601175817141

    • Затем проанализируйте инициализацию подключения к базе данных источника данных druid и connectionInitSqls для выполнения анализа.

      • Источник данных druid инициализирует соединение: (получает sql, выполненный настроенной инициализацией соединения, и выполняет его в свою очередь), а это означает, что если мы настроим utf8mb4, то заданные имена utf8mb4 будут выполняться при инициализации соединения с базой данных.

        • Настраиваем connectionInitSqls в центре конфигурации

          spring.datasource.druid.connection-init-sqls = set names utf8mb4

        • Давайте сначала запустим проект и захватим пакеты данных, которые взаимодействуют с сервером MYSQL при инициализации соединения.

          image-20200602101736618

          image-20200602101636190

      • Ключевые моменты (мы выяснили, перехватив пакеты)

          1. Начальный процесс соединения между источником данных и mysql включает в себя не только 3 рукопожатия, но и множество операций типа запрос-ответ --- sql.
          1. После завершения инициализации соединения мы обнаружили, что настройки кодировки SET NAMES utf8 и установки имен utf8mb4 выполнялись дважды. Второй набор имен utf8mb4 — это когда мы устанавливаем connection-init-sqls.Этот параметр также является настройкой набора символов, которая окончательно вступает в силу для текущего сеанса.Как появился первый раз?Давайте сначала продадим его, а потом проанализируем (Это вопрос на самом деле связан с тем, почему можно использовать тестовую среду разработки)

      • Далее давайте посмотрим, как уровень источника данных druid устанавливает этот набор символов.Благодаря исходному коду автоматической конфигурации druid-spring-boot-starter мы можем легко найти логику выполнения connectionInitSqls с помощью отладки.

        @Configuration
@ConditionalOnClass(DruidDataSource.class)
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class})
@Import({DruidSpringAopConfiguration.class,
    DruidStatViewServletConfiguration.class,
    DruidWebStatFilterConfiguration.class,
    DruidFilterConfiguration.class})
public class DruidDataSourceAutoConfigure {

    private static final Logger LOGGER = LoggerFactory.getLogger(DruidDataSourceAutoConfigure.class);

    @Bean(initMethod = "init")
    @ConditionalOnMissingBean
    public DataSource dataSource() {
        LOGGER.info("Init DruidDataSource");
        return new DruidDataSourceWrapper();
    }
}

        image-20200602100634192

        image-20200602100815075

      • Мы отлаживаем точку останова на Collection initSqls = getConnectionInitSqls();, снова запускаем проект, захватываем пакет и обнаруживаем, что существует только один SET NAMES utf8mb4. Когда мы выпустим эту точку останова, мы обнаружим, что набор имен utf8mb4 до сих пор очень ясен.После того, как источник данных druid инициализирует соединение, он также выполнит инициализацию конфигурации initSql.В этой инициализации пользователь устанавливает набор после имена utf8mb4, указан конечный набор символов.

        image-20200602102925962

  • Вопрос 2:

    • Почему среды тестирования и среды разработки подходят? (Внимательные студенты могут обнаружить, что мы уже упоминали об этом, когда разбирали вопрос 1. Проблема тестовой среды может быть связана с именами первых наборов.) Теперь давайте проверим это.

    • Измените подключение базы данных к среде разработки, перезапустите проект, отмените connectionInitSqls, отладьте точку останова на Collection initSqls = getConnectionInitSqls() и зафиксируйте пакет.

      image-20200602105346805

    • Выясняется, что SET NAMES всего один, и первый раз utf8mb4, а это значит, что даже если мы не передаем друидовский connectionInitSqls, с сессионным подключением в этот раз все в порядке.

    • На этом этапе многие студенты могут подумать, что первый SET NAMES utf8mb4 в среде разработки должен быть связан с глобальной конфигурацией набора символов среды разработки.Мы подключаемся к среде разработки и выполняем проверку sql, что действительно так.

      show global variables like 'character%';

      image-20200602111011779

    • Теперь мы знаем, что первая настройка набора символов связана с глобальной конфигурацией сервера mysql, 👌, это конец? , без нашего продолжения

    • Мы знаем, что настройка набора символов текущего сеанса активно устанавливается клиентом, так по какой причине клиент устанавливает ее именно так? ,смотрим исходник и обнаруживаем,что друид не делает такой операции.До установки друида это действие уже произошло,тогда думаем его надо задать в mysql-connector-java,а потом смотрим исходник проверка.

    • Наш путь отладки такой (процесс поиска конечного кода опущен), мы покажем вам путь отладки, и вы сможете найти его по этому пути.

      image-20200602111824623

      image-20200602112025888

    • Мы обнаружили, что зависит ли SET NAMES utf8mb4 от следующих условий, давайте проанализируем эти два условия:

      boolean utf8mb4Supported = versionMeetsMinimum(5, 5, 2);
boolean useutf8mb4 = utf8mb4Supported && (CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex));

/**
     * Does the version of the MySQL server we are connected to meet the given
     * minimums?
     * 
     * @param major
     * @param minor
     * @param subminor
     */
    boolean versionMeetsMinimum(int major, int minor, int subminor) {
        if (getServerMajorVersion() >= major) {
            if (getServerMajorVersion() == major) {
                if (getServerMinorVersion() >= minor) {
                    if (getServerMinorVersion() == minor) {
                        return (getServerSubMinorVersion() >= subminor);
                    }

                    // newer than major.minor
                    return true;
                }

                // older than major.minor
                return false;
            }

            // newer than major
            return true;
        }

        return false;
    }

  • Условия анализа

    第一个条件 MySQL server version是否比 5.5.2 大
第二个条件 CharsetMapping.UTF8MB4_INDEXES 是否包含 this.io.serverCharsetIndex

只有两个条件同时满足才可以设置 utf8mb4，否则就是utf8
第一个很好理解，而且我们MYSQL SERVER的版本也满足

主要是看下第二个条件
先看下 UTF8MB4_INDEXES 放的是什么？

    image-20200602113052419

        private static final String MYSQL_CHARSET_NAME_utf8mb4 = "utf8mb4";
    Collation[] collation = new Collation[MAP_SIZE];
    collation[1] = new Collation(1, "big5_chinese_ci", 1, MYSQL_CHARSET_NAME_big5);
    ........
    collation[45] = new Collation(45, "utf8mb4_general_ci", 1, MYSQL_CHARSET_NAME_utf8mb4);

    • Я извлек часть исходного кода здесь.Подробности см. в классе com.mysql.jdbc.CharsetMapping.

    • В приведенном выше коде мы обнаруживаем, что UTF8MB4_INDEXES фактически хранит целочисленное значение 45.

    • Оглядываясь назад на это условие, нам нужно только знать, каково значение this.io.serverCharsetIndex.

      CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex)

    • Снова повторите код com.mysql.jdbc.MysqlIO#doHandshake (другой код опущен)

    		/**
     * Initialize communications with the MySQL server. Handles logging on, and
     * handling initial connection errors.
     *  初始化与MySQL服务器的通信。处理登录和初始连接错误。
     * @param user
     * @param password
     * @param database
     * 
     * @throws SQLException
     * @throws CommunicationsException
     */
    void doHandshake(String user, String password, String database) throws SQLException {
        ....
        ....  

        if ((versionMeetsMinimum(4, 1, 1) || ((this.protocolVersion > 9) && (this.serverCapabilities & CLIENT_PROTOCOL_41) != 0))) {

            /* New protocol with 16 bytes to describe server characteristics */
            // read character set (1 byte)
            this.serverCharsetIndex = buf.readByte() & 0xff;
            // read status flags (2 bytes)
            this.serverStatus = buf.readInt();
            checkTransactionState(0);
        }
    }

    • Установлено, что этот атрибут устанавливается, когда соединение с сервером инициализируется, и код рукопожатия пакета перехватывается снова.После завершения трех рукопожатий сервер получает соответствующий ответ и отвечает Язык сервера: utf8mb4 COLLATE utf8mb4_general_ci (45) Настройка набора символов на стороне сервера

      image-20200602114237561

      Откуда взялось 45, на самом деле это поле id нашей базы данных information_schema.COLLATIONS table

      image-20200602114750230

    • Теперь ясно, что при инициализации соединения mysql-connector-java получит глобальную конфигурацию символов mysql текущей базы данных, а затем решит, какой набор символов использовать в соответствии с версией MYSQL SERVER и конфигурацией набора символов MYSQL SERVER. .УСТАНОВИТЬ НАЗВАНИЯ

Суммировать:

  1. Возможность вставки смайликов зависит не только от формата кодировки поля, но и от формата кодировки текущего сеанса

  2. установить utf8mb4

    1. Установите глобальную настройку набора символов, чтобы отображались глобальные переменные, такие как «character%»; -DBA может не помочь вам, после столь долгой работы в сети, вы хотите, чтобы я изменил это?

    2. Установите набор символов текущей настройки сеанса druid connectionInitSqls

    3. Без этой конфигурации вы можете вручную выполнить набор имен utf8mb4 один раз после инициализации пула соединений.

    4. Обновите mysql-connector-java до 5.1.47,5.1.47версияcharacterEncodingпараметр установлен наUTF8/UTF-8, он будет напрямую сопоставлен с utf8mb4, в отличие от более низкой версии, которая также должна полагаться на код, возвращаемый базой данных, и может вступить в силу без перезапуска базы данных.Dev.MySQL.com/doc/теплая заметка…

      image-20200602120352363

Категории