# MySQL 資料庫 [TOC] ## 在Linux環境下登入 **使用`root`帳號登入** ``` mysql -u root -p ``` **使用`fooUser`帳號登入`44.55.66.77`的Database** ``` mysql -u fooUser -p -h 44.55.66.77 ``` ## 檢查編碼 > What is the (default) charset for: - MySQL database - MySQL table - MySQL column To see default collation of the database: ```sql USE db_name; SELECT @@character_set_database, @@collation_database; ``` To see collation of the table: ```sql SHOW TABLE STATUS where name like 'table_name'; ``` **查看全部COLUMN的資訊** ```sql SHOW FULL COLUMNS FROM table_name; ``` 示意圖 ![](https://i.imgur.com/FYua9Qc.png) **查看建表的指令** ```sql SHOW CREATE TABLE table_name; ``` [參考](https://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-mysql-database-table-column-is) ## 更改時區指令 `/*!40103 SET TIME_ZONE='+08:00' */;` 此指令為==時區+8== ## MYSQL 8 預設table AI可能發生碰撞 innodb_autoinc_lock_mode 變量 有三種可能的設置 。設置為 0、1 或 2，分別表示 “傳統”、“連續”或 “交錯”鎖定模式。從 MySQL 8.0 開始，交錯鎖模式 ( innodb_autoinc_lock_mode=2) 是默認設置。在 MySQL 8.0 之前，連續鎖定模式是默認的 ( innodb_autoinc_lock_mode=1)。 > 所以調整設定成`innodb_autoinc_lock_mode=1`即可 > 需要==restart== ## Blob資料轉移錯誤 因:轉移DB 和目標DB不同 將mariaDB轉為mysql後則正常匯入 [DB轉換參考](https://stackoverflow.com/questions/39654428/how-can-i-change-mariadb-to-mysql-in-xampp) ## Table 資料表無痛轉移 1. 鎖定要轉移的資料表 2. 更名資料表 3. 解鎖資料表 ```sql lock tables stopwatch write,stopwatch_2 write;--鎖定要轉移的資料表 ALTER TABLE `stopwatch`--更名資料表 RENAME TO `stopwatch_20201022`; ALTER TABLE `stopwatch_2` RENAME TO `stopwatch`; unlock tables; --解鎖資料表 ``` ## 匯出資料表成.gz壓縮檔 ```shell sudo mysqldump -u root -p level stopwatch_20201022 | gzip > stopwatch_20201022.gz ``` [參考](https://fullstack-tutorials.com/mysql/mysql-export-database-to-gzip-file-in-linux) [參考2](https://stackoverflow.com/questions/22278217/mysqldump-gzip-commands-to-properly-create-a-compressed-file-of-a-mysql-databa) [參考3](https://stackoverflow.com/questions/18741287/mysqldump-exports-only-one-table) ## Query 紀錄 > 紀錄所有SQL Query紀錄 > P.S. 此方式會產生大量資料 注意硬碟空間並使用 先確認紀錄方式 ``` show variables like '%general%'; ``` 結果如下 ![](https://i.imgur.com/PTNlfyh.png) ``` SET GLOBAL log_output = 'TABLE'; ``` or ``` SET GLOBAL log_output = 'FILE'; ``` 成功畫面如下 ![](https://i.imgur.com/RMyhr1d.png) 接著開啟General log ``` SET GLOBAL general_log = 'ON'; ``` ![](https://i.imgur.com/oOVpVh7.png) 之後就可以select  mysql.general_log 這張表或者是在檔案中查詢了 可以再次確認紀錄方式 ``` show variables like '%general%'; ``` ![](https://i.imgur.com/9C0vXCr.png) > 因為general log的量很大(所有connections的所有Query)，所以要不要常態開啟需要自行評估一下 [參考](https://syuhuang.pixnet.net/blog/post/48103365-%5Bmysql%5D-%E6%9F%A5%E8%A9%A2query%E7%9A%84%E6%AD%B7%E5%8F%B2%E7%B4%80%E9%8C%84) ## Slow Query https://www.opencli.com/mysql/mysql-slow-query-log