---
# System prepended metadata

title: MySQL 資料庫

---

# 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