MYSQL全文索引

在MYSQL中，全文索引是在基于文本的列(CHAR、VARCHAR或TEXT列)上创建的，以加快对这些列中包含的数据的查询和DML操作。 全文索引被定义为CREATE TABLE语句的一部分，或者使用ALTER TABLE或CREATE FULL INDEX将其添加到现有表中。

CREATE TABLE `table_name` (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `field_name` TEXT(500)
  FULLTEXT `index_name` (`field_name`)
) ENGINE = InnoDB;

ALTER TABLE `table_name` ADD FULLTEXT INDEX `index_name` (`field_name`);

CREATE FULLTEXT INDEX `index_name` ON `table_name` (`field_name`);

全文搜索的查询使用MATCH() AGAINST语法。

SELECT * FROM `table_name` WHERE MATCH(`field_name`) AGAINST('XXXXX');

Django支持创建MYSQL全文索引的创建

要让Django支持创建MYSQL全文索引，只需继承models.Index类，使用全文索引的创建语句覆写索引的创建逻辑，最后添加到Model类的自定义索引列表中。 之后就可以使用make migrations和migrate命令让Django帮我们自动管理全文索引的创建和迁移。

from django.db import models

class FullTextIndex(models.Index):
  """Fulltext index."""

  create_fulltext_index_sql = (
      'CREATE FULLTEXT INDEX %(name)s ON %(table)s (%(columns)s)%(extra)s')

  def create_sql(self, model, schema_editor, using=''):
    """Gets creating index sql."""
    fields = [
        model._meta.get_field(field_name)
        for field_name, _ in self.fields_orders
    ]
    col_suffixes = [order[1] for order in self.fields_orders]
    return schema_editor._create_index_sql(
        model, fields, name=self.name, using=using,
        db_tablespace=self.db_tablespace, col_suffixes=col_suffixes,
        sql=self.create_fulltext_index_sql)

class User(models.Model):
  """User model."""

  class Meta:
    """Meta."""
    indexes = [FullTextIndex(fields=['desc'])]

  name = models.CharField(max_length=36, unique=True)
  desc = models.TextField()

Django支持创建MYSQL全文索引的查询

因为MYSQL全文索引特殊的查询语法，我们需要在Django ORM中支持MATCH() AGAINST查询语法。 这里定义一个新的查询条件match，支持类似‵Model.objects.filter(field_name__match='xxx')‵的查询。 当然，我们也可以覆盖已有的查询条件比如contains和icontains，无缝衔接已经写好的代码。

class MatchLookup(models.Lookup):
  """Match lookup."""
  lookup_name = 'match'  # You can also set lookup_name = 'contains' to override lookup `contains`.

  def as_sql(self, compiler, connection):
    """As sql statement."""
    lhs, lhs_params = self.process_lhs(compiler, connection)
    rhs, rhs_params = self.process_rhs(compiler, connection)
    params = lhs_params + rhs_params
    return f'MATCH ({lhs}) AGAINST ({rhs} IN BOOLEAN MODE)', params

要让models.TextField支持这种查询语法，需要进行一下注册。

models.TextField.register_lookup(MatchLookup)

当然，为了方便，也可以使用装饰器的方式进行注册。

@models.TextField.register_lookup
class MatchLookup(models.Lookup):
  """Match lookup."""

测试

最后，是一个简单的测试脚本。

import django
from django.conf import settings
from django.db import connection, models
from django.db.backends.base.schema import BaseDatabaseSchemaEditor

settings.configure(
    DATABASES={
        'default': {
            'ENGINE': 'django.db.backends.mysql',
            'NAME': 'NAME',
            'HOST': 'DATABASE_HOST',
            'PORT': 3306,
            'USER': 'root',
            'PASSWORD': 'PASSWORD'
        }
    })
django.setup()


class FullTextIndex(models.Index):
  """Fulltext index."""

  create_fulltext_index_sql = (
      'CREATE FULLTEXT INDEX %(name)s ON %(table)s (%(columns)s)%(extra)s')

  def create_sql(self, model, schema_editor, using=''):
    """Gets creating index sql."""
    fields = [
        model._meta.get_field(field_name)
        for field_name, _ in self.fields_orders
    ]
    col_suffixes = [order[1] for order in self.fields_orders]
    return schema_editor._create_index_sql(
        model, fields, name=self.name, using=using,
        db_tablespace=self.db_tablespace, col_suffixes=col_suffixes,
        sql=self.create_fulltext_index_sql)


@models.TextField.register_lookup
class MatchLookup(models.Lookup):
  """Match lookup."""
  lookup_name = 'match'

  def as_sql(self, compiler, connection):
    """As sql statement."""
    lhs, lhs_params = self.process_lhs(compiler, connection)
    rhs, rhs_params = self.process_rhs(compiler, connection)
    params = lhs_params + rhs_params
    return f'MATCH ({lhs}) AGAINST ({rhs} IN BOOLEAN MODE)', params


class User(models.Model):
  """User model."""

  class Meta:
    """Meta."""
    app_label = 'None'
    db_table = 'user'
    indexes = [FullTextIndex(fields=['desc'])]

  name = models.CharField(max_length=36, unique=True)
  desc = models.TextField()


def main():
  """Main."""
  with BaseDatabaseSchemaEditor(connection) as editor:
    editor.create_model(User)
    editor.add_index(User, FullTextIndex(fields=['desc'], name='idx_desc'))
  User.objects.create(
      name='Mary',
      desc='Kita-Nagato Kaigan Quasi-National Park, Yamaguchi, Japan')

  users = User.objects.filter(desc__match='Kaigan')
  for user in users:
    print(user.name)
    print(user.desc)


if __name__ == '__main__':
  main()