Re: unicode indexing
Posted: Thu Sep 28, 2023 11:59 am
Sending PM failed. "Internal Server Error"
Any field of type 'varchar' should have utf8mb4.show create table links;
| links | CREATE TABLE `links` (
`link_id` int NOT NULL AUTO_INCREMENT,
`site_id` int DEFAULT NULL,
`url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`fulltxt` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`indexdate` date DEFAULT NULL,
`size` float DEFAULT NULL,
`md5sum` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`visible` int DEFAULT '0',
`level` int DEFAULT NULL,
PRIMARY KEY (`link_id`),
KEY `url` (`url`),
KEY `md5key` (`md5sum`),
KEY `site_id` (`site_id`),
CONSTRAINT `links_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=22027 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
show create table keywords;
| keywords | CREATE TABLE `keywords` (
`keyword_id` int NOT NULL AUTO_INCREMENT,
`keyword` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`keyword_id`),
UNIQUE KEY `kw` (`keyword`),
KEY `keyword` (`keyword`(10))
) ENGINE=InnoDB AUTO_INCREMENT=172089 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
andselect keyword from keywords where keyword like 'esp%';
select title, fulltxt from links where url like '%test%'
Code: Select all
| links | CREATE TABLE `links` (
`link_id` int(11) NOT NULL AUTO_INCREMENT,
`site_id` int(11) DEFAULT NULL,
`url` varchar(255) NOT NULL,
`title` varchar(200) DEFAULT NULL,
`description` text,
`fulltxt` longtext,
`indexdate` date DEFAULT NULL,
`size` float DEFAULT NULL,
`md5sum` varchar(32) DEFAULT NULL,
`visible` int(11) DEFAULT '0',
`level` int(11) DEFAULT NULL,
PRIMARY KEY (`link_id`),
KEY `url` (`url`),
KEY `md5key` (`md5sum`),
KEY `site_id` (`site_id`),
CONSTRAINT `links_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6057 DEFAULT CHARSET=utf8mb4 |
Code: Select all
mysql> select keyword from keywords where keyword like 'esp%';
+---------+
| keyword |
+---------+
| espaa |
+---------+
Code: Select all
| Espaa | Espaa Espaa Espaa Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. |
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE links;
DROP TABLE keywords;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE links (
link_id int NOT NULL AUTO_INCREMENT,
site_id int DEFAULT NULL,
url varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
title varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
description text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
fulltxt longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
indexdate date DEFAULT NULL,
size float DEFAULT NULL,
md5sum varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
visible int DEFAULT '0',
level int DEFAULT NULL,
PRIMARY KEY (link_id),
KEY url (url),
KEY md5key (md5sum),
KEY site_id (site_id),
CONSTRAINT links_ibfk_1 FOREIGN KEY (site_id) REFERENCES sites (site_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE keywords (
keyword_id int NOT NULL AUTO_INCREMENT,
keyword varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (keyword_id),
UNIQUE KEY kw (keyword),
KEY keyword (keyword(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
1. "Query OK, 0 rows affected" was a response to each query. Is that OK?mysql> select keyword from keywords where keyword like 'esp%';
+---------+
| keyword |
+---------+
| espaa |
+---------+
1 row in set (0,00 sec)