{"id":6396,"date":"2024-08-19T11:08:15","date_gmt":"2024-08-19T02:08:15","guid":{"rendered":"https:\/\/taitan916.info\/blog\/?p=6396"},"modified":"2024-05-24T22:25:33","modified_gmt":"2024-05-24T13:25:33","slug":"post-6396","status":"publish","type":"post","link":"https:\/\/taitan916.info\/blog\/archives\/6396","title":{"rendered":"MySQL\u3068PHP\u306e\u300cimage-comparator\u300d\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u4f7f\u7528\u3057\u3066\u985e\u4f3c\u753b\u50cf\u691c\u7d22\u3092\u5b9f\u88c5\u3059\u308b\u65b9\u6cd5"},"content":{"rendered":"<p>\u5148\u65e5<a href=\"https:\/\/taitan916.info\/blog\/archives\/6389\" target=\"_blank\" rel=\"noopener\">PHP\u3067\u753b\u50cf\u3092\u6bd4\u8f03\u3057\u3066\u985e\u4f3c\u5ea6\u3092\u7b97\u51fa\u3059\u308b\u300cimage-comparator\u300d\u30e9\u30a4\u30d6\u30e9\u30ea\u306e\u5229\u7528\u65b9\u6cd5<\/a>\u3092\u30e1\u30e2\u3057\u305f\u304c\u3001\u305d\u306e\u307e\u307e\u4f7f\u3046\u3068\u51e6\u7406\u6642\u9593\u304c\u304b\u304b\u308b\u3002\u305d\u306e\u305f\u3081MySQL\u306b\u753b\u50cf\u306e\u30cf\u30c3\u30b7\u30e5\u5024\u3092\u683c\u7d0d\u3057\u3001\u985e\u4f3c\u5ea6\u3092\u6e2c\u5b9a\u3057\u3066\u985e\u4f3c\u3057\u3066\u3044\u308b\u3082\u306e\u9806\u306b\u7d50\u679c\u3092\u51fa\u529b\u3055\u305b\u305f\u3044\u3002\u4ee5\u4e0b\u306b\u5b9f\u88c5\u65b9\u6cd5\u3092\u30e1\u30e2\u3002<\/p>\n<p>&nbsp;<\/p>\n<h2>\u4e8b\u524d\u6e96\u5099<\/h2>\n<h3>\u300cimage-comparator\u300d\u30e9\u30a4\u30d6\u30e9\u30ea\u306e\u5c0e\u5165<\/h3>\n<p><a href=\"https:\/\/taitan916.info\/blog\/archives\/6389\" target=\"_blank\" rel=\"noopener\">\u904e\u53bb\u8a18\u4e8b<\/a>\u3092\u53c2\u7167\u306e\u4e0a\u3001\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u5c0e\u5165\u3057\u3066\u304a\u304f\u3002<\/p>\n<h3>\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210<\/h3>\n<p>\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u305f\u3002image\u306f\u753b\u50cf\u306e\u30cf\u30c3\u30b7\u30e5\u5024\u3067name\u306f\u753b\u50cf\u540d\u3068\u306a\u308b\u3002<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">mysql&gt; SHOW COLUMNS FROM `test_table`;\r\n+-------+--------------+------+-----+---------+----------------+\r\n| Field | Type         | Null | Key | Default | Extra          |\r\n+-------+--------------+------+-----+---------+----------------+\r\n| id    | int          | NO   | PRI | NULL    | auto_increment |\r\n| image | varchar(255) | NO   |     | NULL    |                |\r\n| name  | varchar(255) | NO   |     | NULL    |                |\r\n+-------+--------------+------+-----+---------+----------------+<\/pre>\n<p>&nbsp;<\/p>\n<h2>\u5b9f\u88c5\u65b9\u6cd5<\/h2>\n<h3>\u753b\u50cf\u306e\u30cf\u30c3\u30b7\u30e5\u5024\u3092\u53d6\u5f97<\/h3>\n<p>\u300cimage-comparator\u300d\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u7528\u3044\u3066\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u30cf\u30c3\u30b7\u30e5\u5024\u3092\u53d6\u5f97\u3067\u304d\u308b\u306e\u3067\u3001\u4e0a\u8a18\u30c6\u30fc\u30d6\u30eb\u306b\u683c\u7d0d\u3057\u3066\u304a\u304f\u3002<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">&lt;?php\r\nrequire 'vendor\/autoload.php';\r\nuse SapientPro\\ImageComparator\\ImageComparator;\r\n$imageComparator = new ImageComparator();\r\n\r\n$img = '.\/img\/001.webp';\r\n$hash = $imageComparator-&gt;convertHashToBinaryString($imageComparator-&gt;hashImage($img));\r\nvar_dump($hash); \/\/string(64) \"0000111000001110001001100111110001111100011110000000100011000000\"<\/pre>\n<p>\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u30c7\u30fc\u30bf\u5185\u5bb9\u306b\u306a\u3063\u305f\u3002<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">mysql&gt; SELECT * FROM `test_table`;\r\n+----+------------------------------------------------------------------+----------+\r\n| id | image                                                            | name     |\r\n+----+------------------------------------------------------------------+----------+\r\n|  1 | 0000111000001110001001100111110001111100011110000000100011000000 | 001.webp |\r\n|  2 | 1110000011100000111110111011100000011000001100001110000011000000 | 002.webp |\r\n|  3 | 1111111101100111111000110011001100010011000000010111100111111111 | 003.webp |\r\n|  4 | 0110000000111100000111101101000011100100111111001111110011111110 | 004.webp |\r\n|  5 | 0000010000000100111001001000111110001101111101110010011101111001 | 005.webp |\r\n|  6 | 1100000011010010111110001111100001111101011010010110000000000000 | 006.webp |\r\n|  7 | 1111111111111111111111100000010000000000000110001110000011100001 | 007.webp |\r\n+----+------------------------------------------------------------------+----------+<\/pre>\n<h3>\u691c\u7d22\u7528SQL\u6587<\/h3>\n<p>@target_hash\u306f\u6bd4\u8f03\u3055\u305b\u305f\u3044\u753b\u50cf\u306e\u30cf\u30c3\u30b7\u30e5\u5024\u3067\u4eca\u56de\u306e\u5834\u5408\u306f\u300c001.webp\u300d\u306e\u30cf\u30c3\u30b7\u30e5\u5024\u306b\u306a\u308b\u3002<\/p>\n<p>\u5c1a\u3001SQL\u6587\u3060\u3051\u3067\u307e\u3068\u3081\u308b\u305f\u3081\u5909\u6570\u306e@target_hash\u3068@diff_max\u3092\u8a2d\u5b9a\u3057\u3066\u3044\u308b\u304c\u3001PHP\u304b\u3089SQL\u6587\u3092\u767a\u884c\u3059\u308b\u5834\u5408\u306fPHP\u306e\u5909\u6570\u306b\u9069\u5b9c\u66f8\u304d\u63db\u3048\u308b\u3053\u3068\u3002<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">SET @target_hash:='0000111000001110001001100111110001111100011110000000100011000000';\r\nSET @diff_max:=64;\r\nSELECT\r\n    id,\r\n    (\r\n        (\r\n            @diff_max \r\n            + IF( SUBSTRING(@target_hash, 1, 1) != SUBSTRING(image, 1, 1), -1, 0) \r\n            + IF( SUBSTRING(@target_hash, 2, 1) != SUBSTRING(image, 2, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 3, 1) != SUBSTRING(image, 3, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 4, 1) != SUBSTRING(image, 4, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 5, 1) != SUBSTRING(image, 5, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 6, 1) != SUBSTRING(image, 6, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 7, 1) != SUBSTRING(image, 7, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 8, 1) != SUBSTRING(image, 8, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 9, 1) != SUBSTRING(image, 9, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 10, 1) != SUBSTRING(image, 10, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 11, 1) != SUBSTRING(image, 11, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 12, 1) != SUBSTRING(image, 12, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 13, 1) != SUBSTRING(image,13, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 14, 1) != SUBSTRING(image,14, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 15, 1) != SUBSTRING(image,15, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 16, 1) != SUBSTRING(image,16, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 17, 1) != SUBSTRING(image,17, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 18, 1) != SUBSTRING(image,18, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 19, 1) != SUBSTRING(image,19, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 20, 1) != SUBSTRING(image,20, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 21, 1) != SUBSTRING(image,21, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 22, 1) != SUBSTRING(image,22, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 23, 1) != SUBSTRING(image,23, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 24, 1) != SUBSTRING(image,24, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 25, 1) != SUBSTRING(image,25, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 26, 1) != SUBSTRING(image,26, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 27, 1) != SUBSTRING(image,27, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 28, 1) != SUBSTRING(image,28, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 29, 1) != SUBSTRING(image,29, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 30, 1) != SUBSTRING(image,30, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 31, 1) != SUBSTRING(image,31, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 32, 1) != SUBSTRING(image,32, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 33, 1) != SUBSTRING(image,33, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 34, 1) != SUBSTRING(image,34, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 35, 1) != SUBSTRING(image,35, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 36, 1) != SUBSTRING(image,36, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 37, 1) != SUBSTRING(image,37, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 38, 1) != SUBSTRING(image,38, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 39, 1) != SUBSTRING(image,39, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 40, 1) != SUBSTRING(image,40, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 41, 1) != SUBSTRING(image,41, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 42, 1) != SUBSTRING(image,42, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 43, 1) != SUBSTRING(image,43, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 44, 1) != SUBSTRING(image,44, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 45, 1) != SUBSTRING(image,45, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 46, 1) != SUBSTRING(image,46, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 47, 1) != SUBSTRING(image,47, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 48, 1) != SUBSTRING(image,48, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 49, 1) != SUBSTRING(image,49, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 50, 1) != SUBSTRING(image,50, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 51, 1) != SUBSTRING(image,51, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 52, 1) != SUBSTRING(image,52, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 53, 1) != SUBSTRING(image,53, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 54, 1) != SUBSTRING(image,54, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 55, 1) != SUBSTRING(image,55, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 56, 1) != SUBSTRING(image,56, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 57, 1) != SUBSTRING(image,57, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 58, 1) != SUBSTRING(image,58, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 59, 1) != SUBSTRING(image,59, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 60, 1) != SUBSTRING(image,60, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 61, 1) != SUBSTRING(image,61, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 62, 1) != SUBSTRING(image,62, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 63, 1) != SUBSTRING(image,63, 1), -1, 0)\r\n            + IF( SUBSTRING(@target_hash, 64, 1) != SUBSTRING(image,64, 1), -1, 0)\r\n        ) \/ @diff_max * 100 \r\n    ) AS diff,\r\n    name\r\nFROM\r\n    test_table\r\nORDER BY diff DESC;<\/pre>\n<h3>\u5b9f\u884c\u7d50\u679c<\/h3>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">+----+----------+----------+\r\n| id | diff     | name     |\r\n+----+----------+----------+\r\n|  1 | 100.0000 | 001.webp |\r\n|  6 |  59.3750 | 006.webp |\r\n|  2 |  53.1250 | 002.webp |\r\n|  4 |  53.1250 | 004.webp |\r\n|  7 |  51.5625 | 007.webp |\r\n|  5 |  48.4375 | 005.webp |\r\n|  3 |  39.0625 | 003.webp |\r\n+----+----------+----------+\r\n7 rows in set (0.00 sec)<\/pre>\n<p>&nbsp;<\/p>\n<h2>\u6240\u611f<\/h2>\n<p>\u985e\u4f3c\u5ea6\u8a08\u6e2c\u306b\u3064\u3044\u3066\u306f\u300cimage-comparator\u300d\u30e9\u30a4\u30d6\u30e9\u30ea\u306ecompareHashStrings\u30e1\u30bd\u30c3\u30c9\u306e\u30ed\u30b8\u30c3\u30af\u3092SQL\u6587\u306b\u843d\u3068\u3057\u8fbc\u3093\u3060\u3082\u306e\u306b\u306a\u308b\u3002<\/p>\n<p>PHP\u3060\u3068\u30eb\u30fc\u30d7\u90e8\u5206\u306f\u7c21\u6f54\u306b\u66f8\u3051\u308b\u3082\u306e\u306e\u3001MySQL\u3060\u3068\u96e3\u3057\u3044\u3088\u3046\u3067\u3060\u3044\u3076\u5197\u9577\u306b\u306a\u3063\u305f\u3002\u305f\u3060\u51e6\u7406\u6642\u9593\u306fPHP\u3088\u308a\u3082\u5927\u5e45\u306b\u77ed\u304f\u306a\u3063\u305f\u306e\u3067\u4f7f\u3063\u3066\u3044\u3051\u305d\u3046\u3067\u306f\u3042\u308b\u3002<\/p>\n<p>&nbsp;<\/p>\n<h2>\u53c2\u8003\u30b5\u30a4\u30c8<\/h2>\n<p><a href=\"https:\/\/www.hackerfactor.com\/blog\/index.php?\/archives\/432-Looks-Like-It.html\" target=\"_blank\" rel=\"noopener\">https:\/\/www.hackerfactor.com\/blog\/index.php?\/archives\/432-Looks-Like-It.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5148\u65e5PHP\u3067\u753b\u50cf\u3092\u6bd4\u8f03\u3057\u3066\u985e\u4f3c\u5ea6\u3092\u7b97\u51fa\u3059\u308b\u300cimage-comparator\u300d\u30e9 ... <\/p>\n","protected":false},"author":1,"featured_media":1072,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,5],"tags":[91],"class_list":["post-6396","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-php","category-mysql","tag-image"],"acf":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/posts\/6396","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/comments?post=6396"}],"version-history":[{"count":4,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/posts\/6396\/revisions"}],"predecessor-version":[{"id":6404,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/posts\/6396\/revisions\/6404"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/media\/1072"}],"wp:attachment":[{"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/media?parent=6396"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/categories?post=6396"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/tags?post=6396"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}