MySQL batch modifies character sets and sorting rules of tables and fields in tables

Time:2021-8-4

After copying the new table of the test library to Alibaba cloud MySQL database through Navicat, it was found that the front-end page request query failed.

The default collation for the target database isutf8mb4_0900_ai_ci, all existing tables use this encoding, and the collation of the source database isutf8mb4_unicode_ci, all newly copied tables use the latter.

If the character sets or sorting rules of the two tables are inconsistent during multi table Association query in the database, an error will be reported.

It is inefficient to manually modify the code from Navicat. If you just change the table, it won’t take long, but the problem is that you can’t just change the table. All varchar codes in the table will not follow the table.

Therefore, we still need to take the path of batch operation.

Batch modify field

SELECT
    CONCAT(
        'ALTER TABLE `',
        TABLE_NAME,
        '` MODIFY `',
        COLUMN_NAME,
        '` ',
        DATA_TYPE,
        '(',
        CHARACTER_MAXIMUM_LENGTH,
        ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
        ( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
        ';' 
) 
FROM
    information_schema.COLUMNS 
WHERE
    TABLE_ Schema = 'database name' 
    AND (
    DATA_TYPE = 'varchar' 
    OR DATA_TYPE = 'char')

Batch modification table

SELECT
    CONCAT( 'ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) 
FROM
    information_schema.TABLES 
WHERE
    TABLE_ Schema = 'database name';

Add theutf8mb4utf8mb4_unicode_ciDatabase nameAfter successful execution, copy the execution results, that is, SQL statements, and then execute these SQL statements.

Recommended Today

The selector returned by ngrx store createselector performs one-step debugging of fetching logic

Test source code: import { Component } from ‘@angular/core’; import { createSelector } from ‘@ngrx/store’; export interface State { counter1: number; counter2: number; } export const selectCounter1 = (state: State) => state.counter1; export const selectCounter2 = (state: State) => state.counter2; export const selectTotal = createSelector( selectCounter1, selectCounter2, (counter1, counter2) => counter1 + counter2 ); // […]