Helpex - Trao đổi & giúp đỡ Đăng nhập

Sử dụng kiểu dữ liệu có độ dài thay đổi mà không có độ dài rõ ràng: Lý do và ...

Trong SQL, nếu bạn khai báo một chuỗi trong bất kỳ trong bốn định dạng, CHAR, NCHAR, VARCHAR, hay NVARCHAR, mà không chỉ định chiều dài của nó, chuỗi được đưa ra chiều dài của một ký tự. Nếu bạn ép buộc một chuỗi bằng cách sử dụng CASThoặc CONVERT và mắc lỗi tương tự, thì chuỗi đó sẽ có độ dài là ba mươi ký tự. Tại sao một và ba mươi? Chỉ vì lý do lịch sử, nhưng các RDBMS khác phản ứng theo những cách tương tự.

Trong cả hai trường hợp, bạn có thể gặp rắc rối với việc cắt ngắn chuỗi "im lặng", vì vậy SQL Prompt có một số quy tắc phân tích mã tích hợp để cảnh báo bạn nếu bạn quên chỉ định độ dài chuỗi trong khi khai báo chuỗi có độ dài thay đổi ( BP007 ) và cưỡng chế ( BP008 ).

Sử dụng kiểu dữ liệu có độ dài thay đổi mà không có độ dài rõ ràng: Lý do và ...

Một số nhà phát triển giả định rằng nếu họ bỏ qua độ dài, SQL Server sẽ giải quyết những gì họ cần. Điều này không quá xa vời như người ta tưởng, bởi vì nó đúng như vậy trong các bối cảnh khác, như tôi sẽ tiếp tục chứng minh. Thông thường, bối cảnh cho lỗi này là khi bạn đang nhập từ nguồn dữ liệu mà bạn không biết độ dài chuỗi phù hợp cho các cột và bạn cần tạo bảng đích. Ngoài ra, bạn có thể kết thúc việc sử dụng MAXlàm độ dài, điều này mang lại những rắc rối của riêng nó.

Ở phần sau của bài viết, tôi sẽ chỉ cho bạn một thủ thuật hữu ích để giải quyết vấn đề này. Nó sử dụng tính năng Chèn tập lệnh của Prompt để viết kịch bản cho 1000 hàng đầu tiên của bảng và sau đó chúng tôi thuyết phục SQL Server chỉ định các kiểu dữ liệu và độ dài chuỗi chính xác, bằng cách nhập dữ liệu qua phương thức tạo giá trị bảng (TVC) trong một SELECTINTOcâu lệnh.

Các vấn đề với việc quên độ dài chuỗi

Thông thường, nhưng không phải lúc nào, sai lầm của bạn sẽ nhanh chóng lộ rõ.

CREATE TABLE dbo.Deleteme (MyString VARCHAR NOT null)
  INSERT INTO dbo.Deleteme (MyString) VALUES ('first'),('Second'),('Third')
  /*
  Msg 8152, Level 16, State 14, Line 5
  String or binary data would be truncated.
  The statement has been terminated.
  */
  drop TABLE dbo.Deleteme

Điều này có thể trở nên khó chịu hơn nếu bạn làm điều tương tự với một biến vì chuỗi bị cắt ngắn thành một ký tự duy nhất, nhưng không có lỗi.

DECLARE @MyString VARCHAR ='this ends badly'
  SELECT @MyString
  --no error. Produces value 't'

Điều này cũng đúng với các tham số trong một hàm hoặc thủ tục…

CREATE FUNCTION dbo.deleteme (@param1 VARCHAR, @param2 CHAR)
  RETURNS VARCHAR(100)
  AS
    BEGIN
      RETURN @param1 + @param2;
    END;
  Go
  SELECT dbo.deleteme('this is likely to','go very badly')
  --returns 'tg'

Và trong giá trị trả về của một hàm:

CREATE FUNCTION dbo.deletemeAlso (@param1 VARCHAR(100), @param2 Varchar(100))
  RETURNS VARCHAR
  AS
    BEGIN
      RETURN @param1 + @param2;
    END;
  GO
  SELECT dbo.deletemeAlso('this is likely to','go very badly')
  --returns 't'

Nếu bạn quên khai báo độ dài chuỗi biến trong một CONVERThoặc một CASThoạt động, hành vi sẽ khác. Bạn nhận được một chuỗi ba mươi ký tự.

SELECT Convert(nVARCHAR,'12345678901234567890123456789012345678901234567890' ,113)
  /*
  ------------------------------
  123456789012345678901234567890
  (1 row affected)
  */

Sự khôn ngoan thông thường vào thời điểm đó nói rằng hầu hết các chuỗi cơ sở dữ liệu có độ dài ít hơn ba mươi ký tự. Ngay cả một chuỗi chắc chắn ít hơn ba mươi ký tự cũng được xử lý.

SELECT Convert(CHAR,GetDate() ,113)
  SELECT system_type_name FROM 
   sys.dm_exec_describe_first_result_set(
      'SELECT Convert(VARCHAR,GetDate() ,113)',
       NULL, 1)
  --varchar(30)

Tôi không chắc tại sao mọi người lại muốn sử dụng a VARCHARmà không chỉ định độ dài của nó. Nó có thể là một thói quen bắt gặp từ mã hóa thủ tục. Mọi thứ khác nhau đối với bất kỳ RDBMS nào; bạn có thể đảm bảo rằng các chuỗi được lưu trữ một cách tiết kiệm hoặc chỉ cần vẫy tay một cách nhẹ nhàng và gán chuỗi vào một (MAX)đặc điểm kỹ thuật độ dài, với tất cả các thỏa hiệp về lập chỉ mục và hiệu suất đòi hỏi (các kiểu dữ liệu này không thể được chỉ định làm cột khóa chỉ mục).

Sử dụng kiểu dữ liệu bí danh

Nếu bạn cần độ dài chuỗi mặc định như một phần của thiết kế, bạn có thể tạo kiểu dữ liệu bí danh. Điều này an toàn hơn nhiều:

--create an alias data type
  CREATE TYPE dbo.String  FROM VARCHAR(30) NOT NULL
  GO
  DECLARE @MyString String ='this ends well'
  SELECT @MyString
  ------------------------------
  --this ends well

Tuy nhiên, nhược điểm là, vì một số lý do, bạn không thể sử dụng nó trong một trong hai CASThoặc các CONVERThoạt động với lý do nó không phải là một loại hệ thống xác định.

SELECT Convert(dbo.String,GetDate() ,113)
  SELECT Cast(GetDate() AS String)
  /*Msg 243, Level 16, State 2, Line 40
  Type dbo.String is not a defined system type.
  Msg 243, Level 16, State 2, Line 41
  Type String is not a defined system type.*/

Sử dụng hàm tạo giá trị bảng để chỉ định độ dài chuỗi hợp lý trong quá trình nhập dữ liệu

SQL Server có thể làm tốt hơn nhiều so với việc nhún vai và bỏ cuộc như thế này. Khi cần, nó có thể rất thông minh. Ví dụ, nó có thể tính toán độ dài của kiểu dữ liệu và khả năng vô hiệu của nó trong câu lệnh Bộ tạo giá trị bảng ( TVC ) như thế này.

SELECT name
    FROM (VALUES --one to twenty 
  ('Yan'), --in Lincolnshire dialect
  ('Tyan'),
  ('Tethera'),
  ('Methera'),
  ('Pimp'),
  ('Sethera'),
  ('Lethera'),
  ('Hovera'),
  ('Dovera'),
  ('Dik'),
  ('Yanadik'),
  ('Tyanadik'),
  ('Tetheradik'),
  ('Metheradik'),
  ('Bumfitt'),
  ('Yanabumfit'),
  ('Tyanabumfitt'),
  ('Tetherabumfitt'),
  ('Metherabumfitt'),
  ('Giggot'))f(name)

Nếu bạn SELECTINTOlà một bảng sử dụng TVC này, bạn sẽ có thể thấy rằng nó đã tạo một VARCHARcột với độ dài của các chuỗi dài nhất trong các giá trị trong cột (trong trường hợp này là 'Tetherabumfitt''Metherabumfitt' ; mười bốn ký tự).

Chúng tôi có thể xác minh điều này một cách dễ dàng bằng SELECTINTOmột bảng tạm thời…

SELECT name INTO #MyTemp
    FROM (VALUES --one to twenty 
  ('Yan'), --in Lincolnshire dialect
  ('Tyan'),
   ...etc ...
  ('Giggot'))f(name)

… Và sau đó kiểm tra độ rộng của cột đã tạo…

SELECT system_type_name FROM 
   sys.dm_exec_describe_first_result_set('SELECT * FROM #MyTemp',NULL,1)
   /*system_type_name
     varchar(14)
   */

Điều này gợi ý rằng nếu bạn cần nhập từ một nguồn dựa trên văn bản bên ngoài, chứa một bảng rộng phức tạp với rất nhiều chuỗi, thì cách tốt nhất để đảm bảo rằng bạn nhận được các kiểu dữ liệu chuỗi có độ dài hợp lý là sử dụng TVC.

TVC có giới hạn 1000 hàng khi được sử dụng trong một INSERTINTO…VALUEScâu lệnh và bạn sẽ thấy Lỗi 10738 nếu vượt quá nó. Tuy nhiên, không có giới hạn nào mà tôi có thể phát hiện khi sử dụng TVC trong một SELECTINTOcâu lệnh sử dụng VALUES, như hình minh họa ở trên.

Hãy thử nó. Đây là lần nhập đầu tiên của một thư mục doanh nghiệp, tất nhiên là giả mạo với SQL Data Generator. Nếu bạn muốn chơi cùng, bạn có thể tải xuống tập lệnh xây dựng và tệp .sqlgen từ bài viết Bảng không có chỉ mục nhóm (BP021) của tôi  . Để chứng minh điều này, chúng tôi lấy hàng nghìn hàng đầu tiên vào chế độ xem lưới của SSMS.

SELECT TOP 1000 * FROM bigdirectory

Sử dụng kiểu dữ liệu có độ dài thay đổi mà không có độ dài rõ ràng: Lý do và ...

Bây giờ, nếu bạn có SQL Prompt, bạn sẽ nhấp nhô lên xuống trên ghế khi đọc bài này, bởi vì bạn có một lợi thế khá dễ thương ở đây. Nhấp vào hình vuông trên cùng bên trái của lưới để đánh dấu toàn bộ lô, sau đó nhấp chuột phải và chọn tùy chọn Script as Insert.

Sử dụng kiểu dữ liệu có độ dài thay đổi mà không có độ dài rõ ràng: Lý do và ...

SQL Prompt sử dụng TVC trong một INSERTINTOVALUEScâu lệnh, có nghĩa là nó phải tuân theo giới hạn 1000 hàng. Nếu có nhiều hơn, thì Nhắc nhở chuyển sang sử dụng các INSERTcâu lệnh riêng lẻ .

Trong trường hợp này, chúng tôi sẽ bám sát vào 1000 hàng và điều chỉnh mã này một chút để sử dụng SELECTINTOvới bảng tạm thời, từ TVC, do đó thoát khỏi giới hạn 1000 hàng:

Sử dụng kiểu dữ liệu có độ dài thay đổi mà không có độ dài rõ ràng: Lý do và ...

… Và cứ tiếp tục như vậy cho đến khi…

Sử dụng kiểu dữ liệu có độ dài thay đổi mà không có độ dài rõ ràng: Lý do và ...

Thực thi đoạn mã trên và hãy sử dụng lại sys.dm_exec_describe_first_result_set DMV tiện dụng , để lấy siêu dữ liệu của kết quả cho SELECT*FROM#temptabletruy vấn:

SELECT name + ' ' + system_type_name
         + CASE WHEN is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
    FROM <a id="post-468314-_Hlk535921789"></a>sys.dm_exec_describe_first_result_set('
  SELECT * FROM #temptable', NULL, 1);

Nó chỉ định kiểu dữ liệu, độ dài và khả năng vô hiệu cho mỗi cột, như sau (để điều này hoạt động tốt, bạn sẽ cần một số chuỗi dài và null trong các hàng mẫu của mình):

id int NOT NULL
Name varchar(37) NOT NULL
Address1 varchar(34) NULL
Address2 varchar(23) NULL
Town varchar(22) NOT NULL
City varchar(19) NOT NULL
County varchar(18) NOT NULL
Postcode varchar(8) NOT NULL
Region varchar(22) NOT NULL
BusinessType varchar(55) NULL
Leads varchar(17) NULL
Phone varchar(12) NULL
Fax varchar(12) NULL
Website varchar(56) NULL

Tôi đã thử nghiệm điều này lên đến 10.000 hàng nhưng những người khác đã làm nhiều hơn thế . Khi bạn có kiểu dữ liệu, độ dài và khả năng vô hiệu phù hợp cho tất cả các cột, bạn có thể làm tròn một chút độ dài để cho phép các giá trị ngoại lai, tạo một bảng gọn gàng và sau đó dùng nó để nhập toàn bộ dữ liệu (bốn triệu hàng trong thử nghiệm này).

CREATE TABLE BusinessDirectory
    (
    id INT NOT NULL,
    Name VARCHAR(40) NOT NULL,
    Address1 VARCHAR(40) NULL,
    Address2 VARCHAR(50) NULL,
    Town VARCHAR(30) NOT NULL,
    City VARCHAR(20) NOT NULL,
    County VARCHAR(20) NOT NULL,
    Postcode VARCHAR(8) NOT NULL,
    Region VARCHAR(30) NOT NULL,
    BusinessType VARCHAR(60) NULL,
    Leads VARCHAR(20) NULL,
    Phone VARCHAR(15) NULL,
    Fax VARCHAR(15) NULL,
    Website VARCHAR(60) NULL
    );
  INSERT INTO BusinessDirectory
    (id, Name, Address1, Address2, Town, City, County, Postcode, Region,
  BusinessType, Leads, Phone, Fax, Website)
    SELECT id, Name, Address1, Address2, Town, City, County, 
  Postcode, Region, BusinessType, Leads, Phone, Fax, Website
      FROM BigDirectory;

Nếu bạn không thích phương pháp này, tất nhiên, bạn có thể lấy độ dài thực tế tối đa một cách dễ dàng từ bảng gốc trong trường hợp này.

SELECT Max(Len(Name)), Max(Len(Address1)), Max(Len(Address2)), Max(Len(Town)),
    Max(Len(City)), Max(Len(County)), Max(Len(Postcode)), Max(Len(Region)),
    Max(Len(BusinessType)), Max(Len(Leads)), Max(Len(Phone)), Max(Len(Fax)),
    Max(Len(Website))
    FROM BigDirectory;

Tuy nhiên, việc phát hiện xem các cột có thể trống hay không sẽ phức tạp hơn.

Khi bạn nhập nội dung từ các nguồn dựa trên văn bản bên ngoài, kỹ thuật TVC có thể hữu ích. Tuy nhiên, điểm chính là để cho bạn thấy rằng SQL Server có thể rất giỏi trong việc phát hiện độ dài và khả năng vô hiệu phù hợp của một kiểu dữ liệu chuỗi nếu nó muốn.

Phần kết luận

SQL Server yêu cầu bạn chỉ định độ dài của kiểu dữ liệu chuỗi. Bạn có thể cho rằng vì nó không phản đối khi bạn bỏ đi độ dài, nó bắt buộc phát hiện độ dài cho bạn. Không hoàn toàn không. Nếu bạn khai báo một cột như một CHAR, NCHAR, VARCHARhoặc NVARCHARkhông có chiều dài, SQL Server đọc mà như độ dài của 1. Đây là ví dụ chiều dài ngớ ngẩn đối với bất kỳ chuỗi chiều dài thay đổi mà nó số tiền vô lý vô ích, nhưng chúng tôi đang mắc kẹt với nó.

Nếu bạn xác định một chuỗi biến không có độ dài, nó sẽ chính xác là một sự trả thù thậm chí còn khủng khiếp hơn đối với bạn. Nó không chỉ cho rằng nó có độ dài là 1 ký tự, nó cũng sẽ âm thầm và kín đáo giảm mọi giá trị được gán cho nó cho một ký tự. SQL Server hẳn đã có một tâm trạng ngớ ngẩn khi quyết định giảm varchar thành một ký tự nếu bạn không chỉ định độ dài bởi vì nếu bạn mắc lỗi tương tự khi truyền kiểu dữ liệu đến NVARCHARhoặc VARCHAR, nó sẽ tạo ra một kiểu có độ dài hợp lý hơn trong tổng số 30.

Luôn chỉ định độ dài cho bất kỳ kiểu dữ liệu dựa trên văn bản nào chẳng hạn như NVARCHARhoặc VARCHAR. Không sử dụng quá nhiều MAX thông số kỹ thuật vì cột kết quả sau đó không thể được lập chỉ mục và đi kèm với hành lý hoạt động.

0 hữu ích 0 bình luận 8.2k xem chia sẻ

Có thể bạn quan tâm

loading