瀏覽標籤：

SQL

[MySQL] 資料表結構遷移的好幫手 pt-online-schema-change

作者lex.xu
/
7 月 16, 2020
/
心得筆記

這邊跟大家介紹一套做 DB migration 的好東西 pt-online-schema-change，這是可以讓 DBA 省時省心力的工具，他可以用簡單的指令讓修改資料表結構時不鎖表(可寫入)與接近零 downtime 的好物。

[C#][MSSQL] 連線結束後 Temp Table 沒有自動釋放 !?

作者lex.xu
/
5 月 30, 2019
/
心得筆記

上次 [C#] SQL 資料庫 Connection Pool 連線池觀念釐清有提到可以開啟 Connection Pool 來減少開啟連線的效能耗損，但最近發現有 Stored Procedure 執行後沒有手動 Drop Temp Table，而 .NET 又將連線丟回 Pool 裡面造成 Temp Table 沒有被正常釋放的問題，下面來測試看看是不是真的會有這種情況發生。閱讀更多

[C#] SQL 資料庫 Connection Pool 連線池觀念釐清

作者lex.xu
/
11 月 19, 2018
/
心得筆記

程式對資料庫存取是非常頻繁的操作，平常只會使用而沒有去深入了解底層的實作方式，這次花了點時間真正搞懂了 Connection Pool 的機制。

[MSSQL] SQL Server 的一些眉眉角角 part.2

作者lex.xu
/
11 月 08, 2018
/
心得筆記

小知識

1. 交易中資料表會持續鎖定至交易結束或是連線結束
2. 最外層的交易 ROLLBACK，內層就算 COMMIT 依然會復原變更
3. 巢狀交易過程中的資料表都會鎖定直到交易結束，外層交易 COMMIT 內層交易卻沒有 COMMIT 資料表會被鎖定不會釋放
4. 交易前加上 SET XACT_ABORT ON 可防止過程中發生錯誤沒有 ROLLBACK 造成鎖表的問題

[MSSQL] SQL Server 的一些眉眉角角 part.1

作者lex.xu
/
11 月 01, 2018
/
心得筆記

目前工作 Database 方面主要都在使用 SQL Server，寫了近兩年的 Stored Procedure 也遇到了許多的問題，在這裡簡單筆記一下。

暫存資料表小知識
* # 或 ## 建立的暫存資料表，可從 tempdb.暫存資料表 找到。
* # 建立的暫存資料表只有該連線可以取用；## 則是全部連線均可使用。
* 連線結束時皆會自動刪除，若程式中有 Connection Pool 來管理連線則不會立刻刪除。
* 父層 Stored Procedure 建立的暫存資料表子層也可以使用；子層 Stored Procedure 建立的暫存資料表父層也可以使用
若開啟交易 (Transaction) 但無提交異動 (Commit) 會發生資料表鎖死 (Table Lock) 的問題，切記要提交異動 (Commit)。
使用主鍵來 UPDATE 為資料鎖 (Row Lock)，若使用其他條件則有可能會升級成資料頁鎖(Page Lock) 或資料表鎖 (Table Lock)。
交易 (Transaction) 具有復原機制 (RollBack)，但實際上資料表中的資料已經被異動且鎖定的關係所以無法取得，可利用 WITH(NOLOCK) 來取得被鎖定的髒資料。

未開啟交易時使用 CURSOR 或 UPDATE FROM 若超過五千筆時可能會造成鎖定與死結，可使用 ROW_NUMBER() 排序資料塞入暫存資料表並使用 WHILE 來一筆一筆更新資料避開鎖定，速度雖慢但能減少鎖死。

SET NOCOUNT ON

DECLARE
    @RowNum         INT,
    @RowCount       INT,
    @Temp_Id        INT

SELECT
    ROW_NUMBER() OVER(ORDER BY [Id]) AS RowNum,
    [Id],
    [Name]
INTO
    #Temp
FROM
    [exfast].[dbo].[TableA] WITH(NOLOCK)

SELECT
    @RowNum = 1,
    @RowCount = (SELECT SUM(1) FROM #Temp)

WHILE(@RowNum <= @RowCount)
BEGIN

    SELECT
        @Temp_Id = [Id]
    FROM
        #Temp
    WHERE
        RowNum = @RowNum

    UPDATE
        [exfast].[dbo].[TableA]
    SET
        [Name] = 'dddd'
    WHERE
        [Id] = @Temp_Id

    SET @RowNum = @RowNum + 1

END

DROP TABLE #Temp

SET NOCOUNT ON

DECLARE

@RowNum INT,

@RowCount INT,

@Temp_Id INT

SELECT

ROW_NUMBER() OVER(ORDER BY [Id]) AS RowNum,

[Id],

[Name]

INTO

#Temp

FROM

[exfast].[dbo].[TableA] WITH(NOLOCK)

SELECT

@RowNum = 1,

@RowCount = (SELECT SUM(1) FROM #Temp)

WHILE(@RowNum <= @RowCount)

BEGIN

SELECT

@Temp_Id = [Id]

FROM

#Temp

WHERE

RowNum = @RowNum

UPDATE

[exfast].[dbo].[TableA]

SET

[Name] = 'dddd'

WHERE

[Id] = @Temp_Id

SET @RowNum = @RowNum + 1

END

DROP TABLE #Temp

高負載情況下使用 UPDATE OUTPUT 的方法來取得資料更新前後的狀態，可減少 SELECT 次數提高效率。

DECLARE  @TempA  TABLE
(
    [INSERTED_Id]       INT,
    [INSERTED_Name]     NVARCHAR(32),
    [DELETED_Id]        INT,
    [DELETED_Name]      NVARCHAR(32)
)

UPDATE
    [exfast.Helper].[dbo].[TableA]
SET
    [Name] = 'qqqq'
OUTPUT
    INSERTED.[Id],
    INSERTED.[Name],
    DELETED.[Id],
    DELETED.[Name]
INTO
    @TempA

SELECT * FROM @TempA

DECLARE @TempA TABLE

(

[INSERTED_Id] INT,

[INSERTED_Name] NVARCHAR(32),

[DELETED_Id] INT,

[DELETED_Name] NVARCHAR(32)

)

UPDATE

[exfast.Helper].[dbo].[TableA]

SET

[Name] = 'qqqq'

OUTPUT

INSERTED.[Id],

INSERTED.[Name],

DELETED.[Id],

DELETED.[Name]

INTO

@TempA

SELECT * FROM @TempA

高負載情況下可能會將隔離層級拉高到 Serializable，這時使用交易會發生相同資料表 SELECT UPDATE 中的 Shared Lock 與 Exclusive Lock 撞車造成 DeadLock，可在 SELECT 加上 WITH(UPDLOCK) 來減少此問題。

-- 故意指定隔離層級模擬撞車的情況
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM [exfast.Helper].[dbo].[TableA]

WAITFOR DELAY '00:00:10'

UPDATE
    [exfast.Helper].[dbo].[TableA]
SET
    [Name] = 'cccc'
WHERE
    [Id] = 2
    
ROLLBACK

-- 故意指定隔離層級模擬撞車的情況

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM [exfast.Helper].[dbo].[TableA]

WAITFOR DELAY '00:00:10'

UPDATE

[exfast.Helper].[dbo].[TableA]

SET

[Name] = 'cccc'

WHERE

[Id] = 2

ROLLBACK

[Docker][Linux] SQL Server 安裝筆記

作者lex.xu
/
9 月 15, 2018
/
心得筆記

環境：Window 7

安裝 Docker Toolbox
新增 Volume

docker volume create vol-mssql

1	docker volume create vol-mssql

建立容器

docker run \
--restart=always \
--name mssql \
--mount "source=vol-mssql,target=/var/opt/mssql" \
-e "ACCEPT_EULA=Y" \
-e "SA_PASSWORD=1OCHWiY9O#RF" \
-e "MSSQL_PID=Express" \
-e "MSSQL_COLLATION=Chinese_Taiwan_Stroke_CI_AS" \
-p 1433:1433 \
-d microsoft/mssql-server-linux:latest

docker run \

--restart=always \

--name mssql \

--mount "source=vol-mssql,target=/var/opt/mssql" \

-e "ACCEPT_EULA=Y" \

-e "SA_PASSWORD=1OCHWiY9O#RF" \

-e "MSSQL_PID=Express" \

-e "MSSQL_COLLATION=Chinese_Taiwan_Stroke_CI_AS" \

-p 1433:1433 \

-d microsoft/mssql-server-linux:latest

更新時區

docker exec -ti mssql bash
apt-get update
apt-get install tzdata -y
dpkg-reconfigure tzdata

docker exec -ti mssql bash

apt-get update

apt-get install tzdata -y

dpkg-reconfigure tzdata

參考資料：

[MSSQL] 開啟對外 TCP/IP Port 連線

作者lex.xu
/
3 月 20, 2018
/
心得筆記

電腦管理
服務與應用程式
SQL Server 網路組態
MSSQLSERVER
TCP/IP
通訊協定
啟用：是

這樣就可以用預設 port 1433 連線囉

[MySQL][LeetCode][Medium] 177. Nth Highest Salary

作者lex.xu
/
1 月 15, 2017
/
心得筆記

心得

這題非常有趣，題目要求寫一預存程式輸入N並取出排名第N名的薪水為多少，這題與178. Rank Scores差不多，所以可以直接套用。

題目

Write a SQL query to get the n^th highest salary from the Employee table.

+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+

1
2
3
4
5
6
7

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the n^th highest salary where n = 2 is 200. If there is no n^th highest salary, then the query should return null.

答案

這題我是拿178. Rank Scores的解法直接套上去取N

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT a.Salary
      FROM (SELECT (@row_number:=@row_number + 1) AS Rank, z.Salary
            FROM (  SELECT x.* 
                    FROM Employee AS x 
                    GROUP BY x.Salary
                    ORDER BY x.Salary DESC) AS z
            JOIN (SELECT @row_number := 0) AS y) AS a
      WHERE a.Rank = N
  );
END

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

RETURN (

# Write your MySQL query statement below.

SELECT a.Salary

FROM (SELECT (@row_number:=@row_number + 1) AS Rank, z.Salary

FROM ( SELECT x.*

FROM Employee AS x

GROUP BY x.Salary

ORDER BY x.Salary DESC) AS z

JOIN (SELECT @row_number := 0) AS y) AS a

WHERE a.Rank = N

);

END

這解法是由高至低排序了所有的薪水，再用LIMIT來獲取Nth(因為LIMIT是從0開始所以N必須先減一)，非常易懂的解法。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    DECLARE M INT;
    SET M = N - 1;
    RETURN (
        # Write your MySQL query statement below.
        SELECT Salary
        FROM Employee
        GROUP BY Salary
        ORDER BY Salary DESC
        LIMIT M, 1
  );
END

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

DECLARE M INT;

SET M = N - 1;

RETURN (

# Write your MySQL query statement below.

SELECT Salary

FROM Employee

GROUP BY Salary

ORDER BY Salary DESC

LIMIT M, 1

);

END

參考：

[MySQL][LeetCode][Medium] 178. Rank Scores

[MySQL][LeetCode][Medium] 184. Department Highest Salary

作者lex.xu
/
1 月 15, 2017
/
心得筆記

心得

題目要求找出每個部門薪水最高的員工，若同部門最高薪水有複數則一起顯示，這題我在JOIN部門清單的時候先去把各部門最高薪資數字先撈了出來，這樣在關聯兩張表的時候就可以直接搜尋員工薪水是否等於最高薪資。

問題

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | +----+-------+--------+--------------+

1
2
3
4
5
6
7
8

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+

1
2
3
4
5
6

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+

1
2
3
4
5
6

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

答案

JOIN部門清單的時候先去把各部門最高薪資數字先撈了出來

# Write your MySQL query statement below
SELECT  b.Name AS Department,
        a.Name AS Employee,
        a.Salary AS Salary
FROM Employee AS a
JOIN (  SELECT z.*, (SELECT MAX(y.Salary)
                     FROM Employee AS y
                     WHERE z.Id = y.DepartmentId
                     LIMIT 0, 1) AS MaxSalary
        FROM Department AS z) AS b
ON a.DepartmentId = b.Id AND a.Salary = b.MaxSalary

# Write your MySQL query statement below

SELECT b.Name AS Department,

a.Name AS Employee,

a.Salary AS Salary

FROM Employee AS a

JOIN ( SELECT z.*, (SELECT MAX(y.Salary)

FROM Employee AS y

WHERE z.Id = y.DepartmentId

LIMIT 0, 1) AS MaxSalary

FROM Department AS z) AS b

ON a.DepartmentId = b.Id AND a.Salary = b.MaxSalary

(2017/01/16) 寫 185. Department Top Three Salaries 時後發現的方法

# Write your MySQL query statement below
SELECT  b.Name AS Department,
        a.Name AS Employee,
        a.Salary AS Salary
FROM Employee AS a
JOIN Department AS b
ON a.DepartmentId = b.Id
WHERE ( SELECT COUNT(DISTINCT(z.Salary)) 
        FROM Employee AS z
        WHERE a.DepartmentId = z.DepartmentId
        AND a.Salary < z.Salary) = 0

# Write your MySQL query statement below

SELECT b.Name AS Department,

a.Name AS Employee,

a.Salary AS Salary

FROM Employee AS a

JOIN Department AS b

ON a.DepartmentId = b.Id

WHERE ( SELECT COUNT(DISTINCT(z.Salary))

FROM Employee AS z

WHERE a.DepartmentId = z.DepartmentId

AND a.Salary < z.Salary) = 0

[MySQL][LeetCode][Medium] 178. Rank Scores

作者lex.xu
/
1 月 13, 2017
/
心得筆記

心得

題目要求找出成績排名，如果分數相同的話則相同名次，MySQL不像MSSQL有ROW_NUMBER()可以用，只好用個變數來存了。

問題

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.

+----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+

1
2
3
4
5
6
7
8
9
10

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score):

+-------+------+ | Score | Rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+

1
2
3
4
5
6
7
8
9
10

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

答案

# Write your MySQL query statement below
SELECT a.Score, b.Rank
FROM Scores AS a
JOIN (SELECT (@row_number:=@row_number + 1) AS Rank, z.Score
      FROM (SELECT x.* 
            FROM Scores AS x 
            GROUP BY x.Score
            ORDER BY x.Score DESC) AS z
      JOIN (SELECT @row_number := 0) AS y) AS b
ON a.Score = b.Score  
ORDER BY `b`.`Score` DESC

# Write your MySQL query statement below

SELECT a.Score, b.Rank

FROM Scores AS a

JOIN (SELECT (@row_number:=@row_number + 1) AS Rank, z.Score

FROM (SELECT x.*

FROM Scores AS x

GROUP BY x.Score

ORDER BY x.Score DESC) AS z

JOIN (SELECT @row_number := 0) AS y) AS b

ON a.Score = b.Score

ORDER BY `b`.`Score` DESC