Character encoding in MySQL

Time:2021-8-24

Character encoding problem in MySQL

When writing back-end services to insert data into mysql, errors are always reported due to character coding. Please sort it out.

View encoding

  • View database encoding

    show variables like '%char%';

  • View table code

    Show create table < table name >;

  • View field code

    Show full columns from < table name >;

Modify coding format

Modify database character set:

 ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE …];

Change the default character set and all character columns (char, varchar, text) of the table to a new character set:

 ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE …]
  For example: alter table logtest convert to character set utf8 collate utf8_ general_ ci;

Only modify the default character set of the table:

 ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE…];
  For example: alter table logtest default character set utf8 collate utf8_ general_ ci;

Modify the character set of the field:

 ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE …];
  For example: alter table logtest change Title varchar (100) character set utf8 collate utf8_ general_ ci;

Note:

When modifying the column character set at one time, the default character set of the table is Latin1. As a result, when I use the change statement above to modify, the non empty attribute of the original column will disappear. When I use change and add the non empty and character set settings, I will report a syntax error.

The final solution is:

Modify the default character set of the table firstalter table xxx character set utf8mb4;

At this time, you only need to change the column again, and only specify non empty character sets,show create table xxx;When viewing, you will find that the column has no specified character set, so it will use the default character set of the table.

This work adoptsCC agreement, reprint must indicate the author and the link to this article

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 ); // […]