Defining Functional Indexes in MySQL with Atlas HCL

Question

How can I define functional indexes in MySQL using Atlas HCL syntax?

Answer

MySQL 8.0.13+ supports functional indexes (also called expression-based indexes), which allow you to index the result of an expression rather than just column values. In Atlas HCL syntax, you can define these using the expr field within an index's on block.

JSON Array Cast

For multi-valued indexing of JSON array columns:

schema.hcl

table "construction" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "keywords" {
    type = json
  }
  index "construction_keywords" {
    on {
      expr = "(CAST(keywords AS UNSIGNED ARRAY))"
    }
  }
}

SOUNDEX Function

For phonetic matching using SOUNDEX:

schema.hcl

table "customers" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "last_name" {
    type = varchar(100)
  }
  index "idx_lastname_soundex" {
    on {
      expr = "SOUNDEX(last_name)"
    }
  }
}

JSON Extract

For indexing specific JSON fields:

schema.hcl

table "products" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "attributes" {
    type = json
  }
  index "idx_product_color" {
    on {
      expr = "(CAST(attributes->>'$.color' AS CHAR(50)))"
    }
  }
}

Composite Expression

Combining expression with regular column:

schema.hcl

table "orders" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "order_date" {
    type = datetime
  }
  column "status" {
    type = varchar(50)
  }
  index "idx_year_status" {
    on {
      expr = "YEAR(order_date)"
    }
    on {
      column = column.status
    }
  }
}

For more details on index expressions, see the Atlas HCL documentation.