Câu hỏi SQL Server - Cách chèn bản ghi và đảm bảo nó là duy nhất


Tôi đang cố gắng tìm ra cách tốt nhất để chèn một bản ghi vào một bảng duy nhất nhưng chỉ khi mục không tồn tại. KEY trong trường hợp này là trường NVARCHAR (400). Đối với ví dụ này, hãy giả vờ là tên của một từ trong Từ điển tiếng Anh Oxford / chèn từ điển fav của bạn ở đây. Ngoài ra, tôi đoán tôi sẽ cần phải làm cho trường Word một khóa chính. (bảng cũng sẽ có một mã định danh duy nhất PK).

Vì vậy .. tôi có thể nhận được những từ này mà tôi cần phải thêm vào bảng ...

ví dụ.

  • Con mèo
  • Chó
  • Foo
  • Quán ba
  • PewPew
  • v.v ...

Vì vậy, theo truyền thống, tôi sẽ cố gắng sau (mã giả)

SELECT WordID FROM Words WHERE Word = @Word
IF WordID IS NULL OR WordID <= 0
    INSERT INTO Words VALUES (@Word)

I E. Nếu từ đó không tồn tại, sau đó chèn nó.

Bây giờ .. vấn đề tôi đang lo lắng là chúng tôi đang nhận được rất nhiều lượt truy cập .. như vậy là nó có thể là từ có thể được chèn từ một quá trình ở giữa SELECT và INSERT .. mà sau đó sẽ ném một lỗi ràng buộc ? (ví dụ: a Điều kiện của cuộc đua).

Sau đó tôi nghĩ rằng tôi có thể làm như sau ...

INSERT INTO Words (Word)
SELECT @Word
WHERE NOT EXISTS (SELECT WordID FROM Words WHERE Word = @Word)

về cơ bản, chèn một từ khi nó không tồn tại.

Cú pháp xấu sang một bên, tôi không chắc chắn nếu điều này là xấu hay tốt vì làm thế nào nó khóa xuống bảng (nếu có) và không phải là biểu diễn trên một bảng mà nó nhận được đọc lớn và rất nhiều viết.

Vì vậy, những gì bạn Sql gurus nghĩ / làm gì?

Tôi đã hy vọng có một chèn đơn giản và 'bắt' mà cho bất kỳ lỗi nào ném.


20
2017-11-06 06:43


gốc




Các câu trả lời:


Giải pháp của bạn:

INSERT INTO Words (Word)
    SELECT @Word
WHERE NOT EXISTS (SELECT WordID FROM Words WHERE Word = @Word)

... là tốt như nó được. Bạn có thể đơn giản hóa nó cho điều này:

INSERT INTO Words (Word)
    SELECT @Word
WHERE NOT EXISTS (SELECT * FROM Words WHERE Word = @Word)

... bởi vì EXISTS không thực sự cần trả lại bất kỳ bản ghi nào, vì vậy trình tối ưu hóa truy vấn sẽ không bận tâm khi nhìn vào các trường mà bạn yêu cầu.

Tuy nhiên, như bạn đề cập, điều này không thực sự đặc biệt, bởi vì nó sẽ khóa toàn bộ bảng trong INSERT. Ngoại trừ việc đó, nếu bạn thêm một chỉ mục duy nhất (nó không cần phải là khóa chính) vào Word, thì nó sẽ chỉ cần khóa các trang liên quan.

Lựa chọn tốt nhất của bạn là để mô phỏng tải trọng dự kiến ​​và xem xét hiệu suất với SQL Server Profiler. Như với bất kỳ lĩnh vực nào khác, tối ưu hóa sớm là một điều xấu. Xác định số liệu hiệu suất được chấp nhận và sau đó đo lường trước khi thực hiện bất kỳ thứ gì khác.

Nếu điều đó vẫn không mang lại cho bạn hiệu suất đầy đủ, thì có một loạt các kỹ thuật từ trường lưu trữ dữ liệu có thể giúp ích.


24
2017-11-06 07:31



Có một sự khác biệt hiệu suất giữa EXISTS (SELECT * FROM ...) và EXISTS (SELECT 1 FROM ...)? Tôi luôn luôn có xu hướng sử dụng sau này, để tránh các ngôi sao - là có lợi hoặc là tất cả như nhau? - Tomalak
Tôi nghi ngờ rằng đó là tất cả như nhau, nhưng bạn phải cháy nó trong SQL Server Profiler để chắc chắn. - Roger Lipscombe
Tôi cũng không bao giờ để * trong các lựa chọn của mình, nhưng luôn luôn chỉ định các trường / giá trị trả lại. - Pure.Krome
@ Roger: Ngoài ra, đây không phải là tối ưu hóa sớm (mà tôi hoàn toàn đồng ý với tuyên bố của bạn, về điều đó). Điều này là tối ưu hóa ứng dụng hiện tại của tôi, đó là nhận được thời gian chờ và deadlocks và nó liên quan đến câu lệnh sql này: ( - Pure.Krome
Chỉ cần để tiếp tục cuộc thảo luận này ... u có thể làm mã sql của bạn như LINQ 2 SQL? - Pure.Krome


Tôi nghĩ rằng tôi đã tìm thấy một câu trả lời tốt hơn (hoặc ít nhất là nhanh hơn) cho điều này. Tạo chỉ mục như:

CREATE UNIQUE NONCLUSTERED INDEX [IndexTableUniqueRows] ON [dbo].[table] 
(
    [Col1] ASC,
    [Col2] ASC,

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Bao gồm tất cả các cột xác định tính duy nhất. Phần quan trọng là IGNORE_DUP_KEY = ON. Điều đó biến chèn không độc đáo thành cảnh báo. SSIS bỏ qua những cảnh báo này và bạn vẫn có thể sử dụng tải trọng nhanh.


4
2017-10-20 16:05





Nếu bạn đang sử dụng MS SQL Server, bạn có thể tạo một chỉ mục duy nhất trên các cột trong bảng của bạn cần phải là duy nhất (được ghi lại đây):

CREATE UNIQUE [ CLUSTERED | NONCLUSTERED ] INDEX <index_name>
    ON Words ( word [ ASC | DESC ])

Chỉ định Clustered hoặc là NonClustered, tùy thuộc vào trường hợp của bạn. Ngoài ra, nếu bạn muốn nó được sắp xếp (để cho phép tìm kiếm nhanh hơn), hãy chỉ định ASC hoặc là DESCcho thứ tự sắp xếp.

Xem đây, nếu bạn muốn tìm hiểu thêm về kiến ​​trúc chỉ mục.

Nếu không, bạn có thể sử dụng UNIQUE CONSTRAINTS như tài liệu đây:

ALTER TABLE Words
ADD CONSTRAINT UniqueWord
UNIQUE (Word); 

3
2017-11-06 07:33





Tôi đã có vấn đề tương tự và đây là cách tôi giải quyết nó

insert into Words
( selectWord , Fixword)
SELECT word,'theFixword'
FROM   OldWordsTable
WHERE 
(
    (word LIKE 'junk%') OR
     (word LIKE 'orSomthing') 

)
and word not in 
    (
        SELECT selectWord FROM words WHERE selectWord = word
    ) 

2
2018-05-27 11:11





trong khi ràng buộc duy nhất là một cách để đi bạn cũng có thể sử dụng nó cho logic chèn của bạn: http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005

basicaly bạn không đặt bất kỳ ổ khóa trên bảng dưới đây, do đó không đáng lo ngại về việc đọc trong khi kiểm tra tính tồn tại của bạn sẽ được thực hiện ok.

đó là một mutex trong mã sql.


1
2017-11-06 10:46





Tôi không thể nói đến các đặc điểm của MS SQL, nhưng một điểm của khóa chính trong SQL là đảm bảo tính duy nhất. Vì vậy, theo định nghĩa trong các thuật ngữ SQL chung, một khóa chính là một hoặc nhiều trường duy nhất cho một bảng. Mặc dù có nhiều cách khác nhau để thực thi hành vi này (thay thế mục nhập cũ bằng hành động mới so với mục nhập cũ) Tôi sẽ ngạc nhiên nếu MS SQL không có cơ chế thực thi hành vi này và không phải từ chối mục nhập mới. Chỉ cần chắc chắn rằng bạn đặt khóa chính cho trường Word và nó Nên công việc.

Một lần nữa, mặc dù vậy, tôi từ chối điều này là tất cả từ kiến ​​thức của tôi từ lập trình MySQL và lớp cơ sở dữ liệu của tôi, vì vậy xin lỗi nếu tôi tắt về những phức tạp của MS SQL.


0
2017-11-06 08:29





declare @Error int

begin transaction
  INSERT INTO Words (Word) values(@word)
  set @Error = @@ERROR
  if @Error <> 0 --if error is raised
  begin
      goto LogError
  end
commit transaction
goto ProcEnd

LogError:
rollback transaction

-2
2017-11-06 11:02



Dude - tôi nói trong bài viết mở đầu của tôi rằng tôi không muốn có một chèn câm và sau đó kiểm tra lỗi. đó không phải là IMO rất thông minh. - Pure.Krome