Việc sắp xếp địa chỉ IP theo giá trị số của nó được thực hiện dễ dàng trong SQL Server nhờ chức năng hệ thống PARSENAME. Về mặt kỹ thuật, mục đích chính của chức năng hệ thống PARSENAME là trả về phần được chỉ định của tên đối tượng, với các phần của đối tượng là tên đối tượng, tên chủ sở hữu, tên cơ sở dữ liệu và tên máy chủ. Khi chúng tôi nói đối tượng ở đây, chúng tôi muốn nói đến bảng, dạng xem hoặc bất kỳ đối tượng SQL Server nào
Vì quy ước đặt tên của đối tượng SQL Server khá giống với quy ước đặt tên của địa chỉ IP, giống nhau ở chỗ cả hai đều sử dụng dấu chấm [. ] để tách từng phần và cả hai đều có tối đa 3 dấu chấm, chúng ta có thể sử dụng chức năng hệ thống PARSENAME để lấy các phần khác nhau của địa chỉ IP
Một điều hay về chức năng hệ thống PARSENAME là nó không cho biết liệu một đối tượng theo tên đã chỉ định có tồn tại hay không. Nó chỉ trả về phần được chỉ định của đầu vào đã cho
Để đưa hàm hệ thống PARSENAME vào sử dụng, câu lệnh SELECT sau đây sẽ trả về các địa chỉ IP được sắp xếp theo giá trị số của nó thay vì theo giá trị VARCHAR
Chúng tôi có thể lưu trữ một địa chỉ IP với sự trợ giúp của INT unsigned. Trong khi sử dụng INSERT, hãy thêm INET_ATON[] và với SELECT, hãy thêm INET_NTOA[]. Địa chỉ IP ở định dạng chấm
Hãy để chúng tôi xem một ví dụ
Tạo bảng
mysql> create table IPV4AddressDemo -> [ -> `IPV4Address` INT UNSIGNED -> ]; Query OK, 0 rows affected [0.52 sec]
Chèn địa chỉ IP vào bảng, với INET_ATON
mysql> insert into IPV4AddressDemo values[INET_ATON["120.0.0.1"]]; Query OK, 1 row affected [0.17 sec]
Để hiển thị tất cả các bản ghi
mysql> select *from IPV4AddressDemo;
Sau đây là đầu ra, nhưng chắc chắn chúng tôi muốn nó ở định dạng địa chỉ IP
+-------------+ | IPV4Address | +-------------+ | 2013265921 | +-------------+ 1 row in set [0.00 sec]
Vì đầu ra ở trên đưa ra một chuỗi các số nguyên, nhưng chúng ta có thể chuyển đổi chúng thành định dạng địa chỉ IP ban đầu. Đối với điều đó, hãy sử dụng INET_NTOA
Tôi nhận thấy rằng bạn dự định sử dụng BIGINT cho trường ID, nhưng bạn có thực sự mong muốn thu được hơn 4.294.967.295 giá trị duy nhất không? . Nhưng nếu không, bạn nên sử dụng INT và bắt đầu với giá trị tối thiểu để bạn có thể sử dụng toàn bộ phạm vi của kiểu dữ liệu đó
Nếu muốn, bạn có thể thêm một hoặc nhiều Cột được tính toán KHÔNG liên tục vào bảng này để trả về các biểu diễn văn bản của IPAddress
Các trường Nhóm* được sắp xếp có mục đích đi xuống, từ 8 đến 1, trong bảng để thực hiện SELECT * sẽ trả về các trường theo thứ tự dự kiến. Nhưng chỉ số này tăng lên, từ 1 đến 8, vì đó là cách chúng được điền
IPV4 và IPV6 là các loại dữ liệu trừu tượng lưu trữ địa chỉ máy chủ IPv4 và IPv6 tương ứng ở định dạng nhị phân
IPV4 là địa chỉ máy chủ 4 byte theo ký hiệu thập phân có dấu chấm [bốn số thập phân, mỗi số nằm trong khoảng từ 0 đến 255, được phân tách bằng dấu chấm]. Ví dụ. 172. 16. 254. 1
IPV6 là địa chỉ máy chủ 16 byte trong tám nhóm gồm bốn chữ số thập lục phân được phân tách bằng dấu hai chấm. Ví dụ. 2001. 0db8. 85a3. 0042. 1000. 8a2e. 0370. 7334
Chúng tôi khuyên bạn nên sử dụng các loại dữ liệu IPV4 và IPV6 thay vì văn bản thuần túy để lưu trữ địa chỉ mạng vì chúng cung cấp khả năng kiểm tra lỗi đầu vào cũng như các toán tử và chức năng chuyên dụng
Địa chỉ IPv4 có thể được lưu trữ trong giá trị loại IPv6, nhưng địa chỉ sẽ được lưu trữ dưới dạng địa chỉ IPv6 được ánh xạ IPv4. Khi buộc phải nhập IPV6, không thể phân biệt địa chỉ IPv4 "thuần túy" với địa chỉ IPv4 được ánh xạ—ví dụ: "192. 168. 0. 1" không thể phân biệt được với ". ffff. c0a8. 1" hoặc từ ". ffff. 192. 168. 0. 1"
So sánh giữa hai giá trị IPV4 hoặc hai giá trị IPV6 hoạt động giống như so sánh BYTE [nghĩa là theo byte không dấu]. IPV6 là loại "cao hơn" trong các biểu thức hỗn hợp
Tôi nghĩ trường nhị phân 4 byte là tốt nhất. Bạn có thể sử dụng chức năng chuỗi con để chọn ra các octet và mất ít công việc nhất để chuyển đổi qua lại
Bạn có thể sử dụng chế độ xem hoặc cột được tính toán để chuyển đổi dữ liệu nhị phân trở lại thành địa chỉ IP varchar[15] dưới dạng văn bản nếu bạn cần
Trước tiên, bạn sử dụng tên phân tích cú pháp, sau đó chuyển đổi thành tinyint, sau đó chuyển đổi thành nhị phân [1], sau đó nối
Đây là một ví dụ cơ bản không có xử lý lỗi
CHỨC NĂNG TẠO [dbo]. [ipTxtToBin]
[
@ip varchar[15]
]
TRẢ LẠI nhị phân [4]
BẰNG
BẮT ĐẦU
KHAI BÁO @o1 nhị phân[1], @o2 nhị phân[1], @o3 nhị phân[1], @o4 nhị phân[1]
LỰA CHỌN
@o1 = CHUYỂN ĐỔI[nhị phân[1],CHUYỂN ĐỔI[tinyint,PARSENAME[@ip,4]]],
@o2 = CHUYỂN ĐỔI[nhị phân[1],CHUYỂN ĐỔI[tinyint,PARSENAME[@ip,3]]],
@o3 = CHUYỂN ĐỔI[nhị phân[1],CHUYỂN ĐỔI[tinyint,PARSENAME[@ip,2]]],
@o4 = CHUYỂN ĐỔI[nhị phân[1],CHUYỂN ĐỔI[tinyint,PARSENAME[@ip,1]]]
TRỞ LẠI @o1+@o2+@o3+@o4
CHẤM DỨT
Và để kiểm tra nó
khai báo @ip VARCHAR[15]
đặt @ip = '255. 128. 64. 32'
khai báo @bin nhị phân [4]
chọn @bin = [dbo]. [ipTxtToBin] [@ip]
chọn @ip làm ip
, @bin là thùng
, chuyển đổi[tinyint,substring[@bin,1,1]] thành octet1
, chuyển đổi[tinyint,substring[@bin,2,1]] thành octet2
, chuyển đổi[tinyint,substring[@bin,3,1]] thành octet3
, chuyển đổi[tinyint,substring[@bin,4,1]] thành octet4
Kết quả
ip. thùng rác. octet1. octet2. octet3. octet4
255. 128. 64. 32. 0xFF804020. 255. 128. 64. 32
Bạn tiết kiệm từ 3 đến 11 byte cho mỗi địa chỉ IP. Bạn có thể lọc theo phạm vi bằng cách sử dụng các đánh giá số hoặc thông qua các hoạt động theo bit