[PHP開發用] MySQL 指令收集與整理


取得資料庫資訊

MySQL提供「SHOW」指定讓你取得跟資料庫相關的資訊,執行下列的指令可以取得當前帳號 MySQL 伺服器中該帳號可以存取的所有資料庫的名稱。

想要知道目前存在那些資料庫名稱可以這樣(注意,只會列出該 MYSQL 帳號權限顯示允許存取的資料庫):

列出所有資料庫

SHOW DATABASES;
#以下行效果同上
SHOW SCHEMAS;
#在MySQL中,"DATABASES" 與 "SCHEMA"是同義的。SQL語法中可以使用關鍵字 SCHEMA 替代 DATABASE ,例如使用CREATE SCHEMA 等同於 CREATE DATABASE。

結果將列出所有資料庫名稱。

列出所有資料庫名稱為 my 開頭的

SHOW DATABASES LIKE 'my%';
 

列出該資料庫所有資料表名稱

SHOW TABLES FROM [db_name]

列出該資料表所有欄位名稱

SHOW COLUMNS FROM [table_name] FROM [db_name]
範例:SHOW COLUMNS FROM article FROM test
結果:
Field Type Null Key Default Extra
id int(10) unsigned NO PRI   auto_increment
user_id int(10) unsigned YES   0  
title varchar(50) NO      
content varchar(50) NO      
published_at int(10) unsigned YES   0  
 
SHOW FIELDS FROM  [table_name]
範例:SHOW FIELDS FROM article
結果:
Field Type Null Key Default Extra
id int(10) unsigned NO PRI   auto_increment
user_id int(10) unsigned YES   0  
title varchar(50) NO      
content varchar(50) NO      
published_at int(10) unsigned YES   0  
 
DESCRIBE [table_name]
 
EXPLAIN [table_name]
 

列出該資料表所有索引資訊

SHOW INDEX FROM [table_name] FROM [db_name]
範例:SHOW INDEX FROM article FROM test
結果:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
article 0 PRIMARY 1 id A 2       BTREE    
 

列出資料表的相關資訊

SHOW TABLE STATUS
SHOW TABLE STATUS FROM [db_name]  [LIKE ...]
範例:SHOW TABLE STATUS FROM test
結果:
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
article InnoDB 10 Compact 2 8192 16384 0 0 0 4 2017-05-03 17:31:33     latin1_swedish_ci      
 

顯示 MySQL 相關參數設定

SHOW VARIABLES [LIKE ...]
 

顯示資料庫語系設定資訊

SHOW VARIABLES LIKE '%character%'
結果:
Variable_name Value
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database latin1
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8mb4
character_set_system utf8
character_sets_dir C:\mysql\share\charsets\

列出與 MySQL 連線的 threads 狀態

SHOW PROCESSLIST
結果:
Id User Host db Command Time State Info Progress
11405 test localhost:56898 test Query 0 init SHOW PROCESSLIST 0.000
 

列出 MySQL 目前的狀態

SHOW STATUS


 

產生創建表格的程式碼

有時候已經有一個存在的表格,想要產生一組完整的語法,使得可以備用後續還原,或是複製到別的資料庫。
這時候就可以用以下語法。
To generate a create table script for an existing table.
SHOW CREATE TABLE {tablename}
範例:
SHOW  CREATE TABLE `article`;

輸出結果:
Table Create Table
article CREATE TABLE `article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT '0',
  `title` varchar(50) NOT NULL,
  `content` varchar(50) NOT NULL,
  `published_at` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
# 注意,以上 article 資料表結果是台灣金站已經預先建立好的資料庫資料。
 

列出 MySQL 伺服器中所有資料庫欄位資訊

MySQL資料庫伺服器有一個「系統資料庫」(information_schema),這個資料庫欄位儲存伺服器所有重要的資訊。

跟資料庫有關的資訊都會紀錄在「SCHEMATA」表格中,所以你可以使用查詢敘述取得所有資料庫的相關資訊:

select * from information_schema.SCHEMATA

輸出結果:
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
def information_schema utf8 utf8_general_ci  
def test latin1 latin1_swedish_ci  


當然還可以進階指定讀取某個資料庫底下的某個資料表資訊:

select * from information_schema.columns 
where table_name = '[資料表]' and table_schema = '[資料庫]'
範例:
select * from information_schema.columns 
where table_name = 'article' and table_schema = 'test'

輸出結果:
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT
def test article id 1   NO int     10 0       int(10) unsigned PRI auto_increment select,insert,update,references  
def test article user_id 2 0 YES int     10 0       int(10) unsigned     select,insert,update,references  
def test article title 3   NO varchar 50 50       latin1 latin1_swedish_ci varchar(50)     select,insert,update,references  
def test article content 4   NO varchar 50 50       latin1 latin1_swedish_ci varchar(50)     select,insert,update,references  
def test article published_at 5 0 YES int     10 0       int(10) unsigned     select,insert,update,references  
 

單純只列出資料表中的欄位資訊

例如我只想要 column_name 而已,不用像上述一樣列出完整的資訊,則可以:

Show columns but exclude everything except the field names
SELECT column_name
FROM information_schema.columns
WHERE  table_name = '[table_name]'
   AND table_schema = '[db_name]'
範例:
SELECT column_name
FROM information_schema.columns
WHERE  table_name = 'article'
   AND table_schema = 'test'

輸出結果:
column_name
id
user_id
title
content
published_at

這個結果類似於 SELECT * FROM [table_name],列出所有欄位。

 

鎖定資料表

當進行資料表檢查或修補時,鎖定資料表可確保資料表的安全
(READ:唯讀狀態、WRITE:無法寫入也無法讀取)
LOCK TABLE table_name READ;
FLUSH TABLES;
 

資料表解除鎖定 

UNLOCK TABLE
 

MySQL 帳號新增

快速新增一組帳號並授權可以存取某個資料庫的所有資料。

以下將開設一組 MySQL 帳號名稱:test,密碼:123456,允許此 test 帳戶可以存取 test_database 資料庫。
並且對此資料庫擁有 SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE 等...權限。
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
GRANT USAGE ON *.* TO 'test'@'localhost';
GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES  ON `test_database`.* TO 'test'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

#SHOW GRANTS FOR 'test'@'localhost';
當修改過 mysql 資料庫中的權限設定時,需重新啟動 MySQL 才會生效。
因此可以看到最後行使用 FLUSH PRIVILEGES 來刷新系統。

也可以用以下方式重新啟動 MySQL 權限控制系統 privilege system:
mysqladmin flush-privileges
 

MySQL 帳號刪除

刪除某個連線帳戶。

DROP USER 'test'@'localhost';
FLUSH PRIVILEGES;

 

回上一頁

相關文章:

喜歡這篇文章嗎?立即分享

Posted : / Views: 544
Last updated :2017-10-27