MySQL IS_IPV4() and IS_IPV6() Functions

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)

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


Syntax of MySQL IS_IPV6()

IS_IPV6(ip_address)

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?';

And the output is –

Isipv4 Basic Example 1

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?';

And the output is –

MySQL IS_IPV4 Basic Example 2

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?';

And the output is –

MySQL IS_IPV4 Null

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?';

And the output is –

Isipv4 If

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?';

And the output is –

Isipv6 Basic Example 1

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?';

And the output is –

MySQL IS_IPV6 Basic Example 2
Isipv6 Basic Example 2

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?';

And the output is –

MySQL IS_IPV6 Null

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?';

And the output is –

Isipv6 If

Working With Tables

Consider the below ‘PCs’ table. 

Ipv4 Ipv6 PCs Table
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;

And the output is –

Ipv4 Ipv6 PCs Table Example

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