How do I select IP addresses or Visitor ID from the SQL database and convert binary table fields?

In the database, the IP addresses and Visitor IDs are stored in Binary form for storage efficiency. To display these values correctly you can perform conversion of the Binary data, as in the following SQL query:

SELECT INET6_NTOA(`location_ip`) as ip, conv(hex(idvisitor), 16, 16) as visitorId FROM matomo_log_visit;

If you use MySQL 5.6.2 or earlier, you will have to use this SQL query instead (which only supports IPv4):

SELECT inet_ntoa(conv(hex(location_ip), 16, 10)) as ip, conv(hex(idvisitor), 16, 16) as visitorId FROM matomo_log_visit;

To select a specific visit if you know the idvisitor you can run this SQL query:

SELECT * FROM log_visit WHERE lower(conv(hex(idvisitor), 16, 16)) = 'a43f52349c8890af';

You may also be interested in: How do I select all users within a range of IP addresses?