MySQL IS_IPV4() and IS_IPV6() Functions

In this tutorial, we will study the MySQL IS_IPV4() and IS_IPV6() functions. If your friend wishes to send you a parcel, they would want to know your address so that it reaches you safely.

In a similar way, computers or hardware have addresses called IP addresses. IP stands for Internet Protocol. An IP address is an address of your network hardware. It helps in connecting your computer to other devices on your network and all over the world. There are two IP address types – IPv4 and IPv6.

The v4 in IPv4 stands for version 4. Similarly, the v6 in IPv6 stands for version 6. Now you would have a question, why are there two types of IP addresses?

Long story short, IPv6 was the norm followed in IP addresses, however, due to the boom in technology, we started running out of IPv4 addresses and hence, IPv6 addresses were introduced.

Here is an example of an IPv4 address – ‘192.168.4.4’. Here is an example of an IPv6 address – 4ggr:1925:5656:7:600:t4tt:tc54:98vt.

Since IP addresses are so important, there should be a way to check if an IP address is of type IPv4 or IPv6. MySQL provides us with the IS_IPV4() and IS_IPV6() functions.

• The IS_IPV4() function checks if an IP address is of type IPv4 or not. If it is an IPv4 address, it returns 1, otherwise it returns 0.
• The IS_IPV6() function checks if an IP address is of type IPv6 or not. If it is an IPv6 address, it returns 1, otherwise it returns 0.

Syntax of MySQL IS_IPV4()

IS_IPV4(ip_address)Code language: SQL (Structured Query Language) (sql)

Where, ‘ip_address’ is the value that needs to be checked for IPv4 type.

Syntax of MySQL IS_IPV6()

IS_IPV6(ip_address)Code language: SQL (Structured Query Language) (sql)

Where, ‘ip_address’ is the value that needs to be checked for IPv6 type.

Examples of MySQL IS_IPV4()

Let us kick things off with some basic examples. Let us check if the value – ‘192.168.4.4’ is IPv4 or not. We will use the SELECT statement and aliases in our query. The query is –

SELECT IS_IPV4('192.168.4.4') AS 'Is IPv4?';Code language: SQL (Structured Query Language) (sql)

And the output is –

That means the value is indeed an IPv4 type IP address. Next, let us see if ‘445.4.45.4’ is IPv4 or not. The query is –

SELECT IS_IPV4('445.4.45.4') AS 'Is IPv4?';Code language: SQL (Structured Query Language) (sql)

And the output is –

This means the above value is not IPv4 type. IPv4 addresses cannot have a number greater than 255 in any one of the numbers Since 445 is greater than 255, the value is not IPv4.

MySQL IS_IPV4() With NULL Value

MySQL IS_IPV4() returns 0 if we pass a NULL value to it. This is because NULL clearly is not an IP address in the first place. Below is an example for this.

SELECT IS_IPV4(NULL) AS 'Is IPv4?';Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL IS_IPV4() With IF() Function

Let us make the output from the IS_IPV4() function more understandable using the IF() function. Here’s how you can do it.

SELECT IF(IS_IPV4('192.168.4.4')=1, "Yes", "No") AS 'Is IPv4?';Code language: SQL (Structured Query Language) (sql)

And the output is –

Examples of MySQL IS_IPV6()

Let us check if the value – ‘2001:0db8:85a3:0000:0000:8a2e:0370:7334’ is IPv6 or not. The query is –

SELECT IS_IPV6('2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS 'Is IPv6?';Code language: SQL (Structured Query Language) (sql)

And the output is –

That means the value is indeed an IPv6 type IP address. Next, let us see if ‘192.168.4.4’ is IPv6 or not. The query is –

SELECT IS_IPV6('192.168.4.4') AS 'Is IPv6?';Code language: SQL (Structured Query Language) (sql)

And the output is –

This means the above value is not IPv6 type.

MySQL IS_IPV6() With NULL Value

IS_IPV6() returns 0 if we pass a NULL value to it. This is because NULL clearly is not an IP address in the first place. Below is an example for this.

SELECT IS_IPV6(NULL) AS 'Is IPv6?';Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL IS_IPV6() With IF() Function

Let us make the output from the IS_IPV6() function more understandable using the IF() function. Here’s how you can do it.

SELECT IF(IS_IPV6('::00')=1, "Yes", "No") AS 'Is IPv6?';Code language: SQL (Structured Query Language) (sql)

And the output is –

Working With Tables

Consider the below ‘PCs’ table.

Let us display the PC ID, Name and provide two checks for the IP address. One column should check if the IP address is IPv4 and the other should check if the IP address is IPv6. A proper readable output should be printed in both columns. Here is the query –

SELECT ID, Name, 
IF(IS_IPV4(IP_Address)=1, "Yes", "No") AS 'IPv4', 
IF(IS_IPV6(IP_Address)=1, "Yes", "No") AS 'IPv6' 
FROM PCs;Code language: SQL (Structured Query Language) (sql)

And the output is –

Conclusion

Databases are widely used in the domain of networking and that is where functions like IS_IPV4() and IS_IPV6() find prominence. I encourage you to go through the below references.

References

• MySQL Official Documentation on IS_IPV4() and IS_IPV6() functions.