How to operate MySql database with gorm

1. Setting case sensitivity of fields in the table

When using gorm query, the account names A and a may be the same, because MySQL is case-insensitive by default.

1. Causes of the problem

MySQL is not case sensitive under Windows .
The case rules for MySQL database names, table names, column names, and aliases under Linux are as follows:

1. Database names and table names are strictly case-sensitive;
2. The table alias is strictly case-sensitive;
3. Column names and column aliases are case-insensitive in all cases;
4. Variable names are also strictly case-sensitive;

MySQL is case-insensitive when querying strings. When compiling MySQL, the ISO-8859 character set is generally used as the default character set. This character set is case-insensitive. Therefore, the case conversion of Chinese encoded characters during the comparison process causes this phenomenon.

2. Sorting rules in MySQL

utf8_bin stores each character in a string as binary data, distinguishing between uppercase and lowercase letters. utf8_genera_ci is case-insensitive. ci is the abbreviation of case insensitive, which means it is case-insensitive. utf8_general_cs is case-sensitive. cs is the abbreviation of case sensitive. ( Note: In MySQL 5.6.10, utf8_genral_cs is not supported!!!

3. Solution

1. Binary keyword

Simply modify the SQL query statement and add the binary keyword before the field to be queried. (Not recommended)

1. Add the binary keyword before each condition

select * from user where binary username = 'admin' and binary password = 'admin';

2. Surround the parameters with binary('')

select * from user where username like binary('admin') and password like binary('admin');

2. Modify Collation properties

When creating a table, directly set the collate property of the table to utf8_general_cs or utf8_bin; if the table has already been created, directly modify the Collation property of the field to utf8_general_cs or utf8_bin.

1. Modify the table structure

ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;

2. Modify fields (use gorm to set the fields in the table to be case-sensitive)

`gorm:"unique" sql:"type:VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin"`

This is the end of this article about how to use gorm to operate MySql database. For more information about how to use gorm to operate MySql database, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!