首页 > 代码库 > You can add an index on a column that can have NULL values if you are using the MyISAM, InnoDB, or MEMORY storage engine.

You can add an index on a column that can have NULL values if you are using the MyISAM, InnoDB, or MEMORY storage engine.

2024-09-17 11:55:54   220人阅读

w

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

技术分享

MySQL :: MySQL 5.7 Reference Manual :: B.5.4.3 Problems with NULL Values
https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html

 

You can add an index on a column that can have NULL values if you are using the MyISAM, InnoDB, or MEMORY storage engine. Otherwise, you must declare an indexed column NOT NULL, and you cannot insert NULL into the column.

 

 

MySQL :: MySQL 5.7 Reference Manual :: 14.1.14 CREATE INDEX Syntax
https://dev.mysql.com/doc/refman/5.7/en/create-index.html

 

The MyISAM, InnoDB, NDB, and ARCHIVE storage engines support spatial columns such as (POINT and GEOMETRY. (Section 12.5, “Extensions for Spatial Data”, describes the spatial data types.) However, support for spatial column indexing varies among engines. Spatial and nonspatial indexes are available according to the following rules.

Spatial indexes (created using SPATIAL INDEX) have these characteristics:

  • Available only for MyISAM and InnoDB tables. Specifying SPATIAL INDEX for other storage engines results in an error.

  • Indexed columns must be NOT NULL.

  • Column prefix lengths are prohibited. The full width of each column is indexed.

Characteristics of nonspatial indexes (created with INDEX, UNIQUE, or PRIMARY KEY):

  • Permitted for any storage engine that supports spatial columns except ARCHIVE.

  • Columns can be NULL unless the index is a primary key.

  • For each spatial column in a non-SPATIAL index except POINT columns, a column prefix length must be specified. (This is the same requirement as for indexed BLOB columns.) The prefix length is given in bytes.

  • The index type for a non-SPATIAL index depends on the storage engine. Currently, B-tree is used.

  • You can add an index on a column that can have NULL values only for InnoDB, MyISAM, and MEMORY tables.

  • You can add an index on a BLOB or TEXT column only for using the InnoDB and MyISAM tables.

  • When the innodb_stats_persistent setting is enabled, run the ANALYZE TABLE statement for an InnoDB table after creating an index on that table.

 

You can add an index on a column that can have NULL values if you are using the MyISAM, InnoDB, or MEMORY storage engine.


联系
我们