Linux Shell MySQL

Time:2020-10-18

Modify auto increment starting value

ALTER TABLE table AUTO_INCREMENT = 100000;

Create user

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

show user

SELECT * FROM mysql.USER;

View permissions

SHOW GRANTS FOR 'newuser'@'localhost';

View character encoding list

SHOW CHARACTER SET;

View a list of character collations

SHOW COLLATION;

show database

SHOW DATABASES;

View database character encoding and character collation

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'database_name';

# OR

USE 'database_name';
SELECT @@character_set_database, @@collation_database;

Create database

CREATE DATABASE IF NOT EXISTS 'database_name'
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Utf8 has been abandoned because it supports up to 3-byte characters, while utf8mb4 can support 4-byte characters.
Collate will affect index creation, search sorting and comparison for character types.

  • CS – case sensitive – case sensitive
  • CI – case insensitive – case insensitive
  • Ai – accent insensitive – pronunciation independent

Utf8mb4 is recommended_ unicode_ Ci, relative to the default utf8mb4_ general_ Ci is more in line with the western language habits, other no difference.
From MySQL 8.0, utf8mb4 is used by default_ 0900_ Ai_ Ci, 0900 refers to the Unicode collation algorithm version.

Select database

USE 'database_name';

View selected database

SELECT DATABASE();

View database tables

SHOW TABLES;

Display table structure

DESCRIBE mysql.USER;

Using MySQL_ native_ Password password login

# MySQL
ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';

# MariaDB
UPDATE `mysql`.`user` SET plugin="mysql_native_password";