MySQL notes, mysql

MySQL notes, mysql
Modify Field type and length

Description: alter table name modify column field name type;
Example: alter table player modify column name varchar (50 );
Note:

• Exercise caution when modifying the type, which may cause errors in the original data.
• To modify the length, ensure that the existing data and existing data are not short, so as to ensure that the original data is not wrong.

Set the field value to be case sensitive

Note: mysql field values are case-insensitive by default. If you want to query the case-sensitive values of Time Zone Field Values, you must set the BINARY Attribute for the field values.
Example:

• Set at creation: create table player (name VARCHAR (64) BINARY );
• Alter: alter table player modify column name VARCHAR (64) BINARY;

View table structure in mysql

Note: Use the desc command
Example: desc player

Back up and restore a database using mysqldump

Description: exports the entire database. Mysqldump-u username-p Password Database Name> exported file name
Example: mysqldump-u root-p pwd mydatabase> idata. SQL

Description: exports a table. Mysqldump-u user name-p Password Database Name Table Name> exported file name
Example: mysqldump-u root-p pwd mydatabase users> idata_user. SQL

Description: exports a database structure. Mysqldump-u username-p-d Password Database Name> exported file name. -D means no data.
Example: mysqldump-u root-p pwd-d mydatabase> idata. SQL

(Restore the database, enter the directory where the backup file is located, and then enter mysql, select the corresponding database, and use)sourceCommand.

mysql -u root -p mysql>use mydatabase;mysql>source idata.sql;

Reference website

Solve the 1449 error of mysqldump backup database

Note: For example, use the dog account on the DogHome server to back up data and import the data to the CatHome server. When the CatHome server uses cat for backup, a prompt is displayed, indicating that you must use the dog account for backup. cat has no permission.

Mysqldump: Got error: 1449: The user specified as a definer ('dog '@' % ') does not exist when using LOCK TABLES

The solution is to grant the dog permission to cat on the server.

Example:

mysql>grant all privileges on dog.* to 'dog'@'%' identified by 'cat';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)