Support expression indexes for MySQL

MySQL 8.0.13 and higher supports functional key parts that index
expression values rather than column or column prefix values.

https://dev.mysql.com/doc/refman/8.0/en/create-index.html

2 files changed, 34 insertions, 4 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
index 0f73641369..13c799b64a 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -72,6 +72,10 @@ module ActiveRecord
         !mariadb? && version >= "8.0.1"
       end
 
+      def supports_expression_index?
+        !mariadb? && version >= "8.0.13"
+      end
+
       def supports_transaction_isolation?
         true
       end
diff --git a/activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb
index e167c01802..65e9a1fda5 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb
@@ -35,13 +35,39 @@ module ActiveRecord
                 ]
               end
 
-              indexes.last[-2] << row[:Column_name]
-              indexes.last[-1][:lengths][row[:Column_name]] = row[:Sub_part].to_i if row[:Sub_part]
-              indexes.last[-1][:orders].merge!(row[:Column_name] => :desc) if row[:Collation] == "D"
+              if row[:Expression]
+                expression = row[:Expression]
+                expression = +"(#{expression})" unless expression.start_with?("(")
+                indexes.last[-2] << expression
+                indexes.last[-1][:expressions] ||= {}
+                indexes.last[-1][:expressions][expression] = expression
+                indexes.last[-1][:orders][expression] = :desc if row[:Collation] == "D"
+              else
+                indexes.last[-2] << row[:Column_name]
+                indexes.last[-1][:lengths][row[:Column_name]] = row[:Sub_part].to_i if row[:Sub_part]
+                indexes.last[-1][:orders][row[:Column_name]] = :desc if row[:Collation] == "D"
+              end
             end
           end
 
-          indexes.map { |index| IndexDefinition.new(*index) }
+          indexes.map do |index|
+            options = index.last
+
+            if expressions = options.delete(:expressions)
+              orders = options.delete(:orders)
+              lengths = options.delete(:lengths)
+
+              columns = index[-2].map { |name|
+                [ name.to_sym, expressions[name] || +quote_column_name(name) ]
+              }.to_h
+
+              index[-2] = add_options_for_index_columns(
+                columns, order: orders, length: lengths
+              ).values.join(", ")
+            end
+
+            IndexDefinition.new(*index)
+          end
         end
 
         def remove_column(table_name, column_name, type = nil, options = {})