Shulou(Shulou.com)06/01 Report--

Let's take a look at the specific steps to quickly create an index for MySQL millions of data. I believe you will benefit a lot after reading it. There are not many words in the text. I hope this short article is what you want. Start the preparatory work.

User structure:

CREATE TABLE `users` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar (255) CHARACTER SET utf8mb4 DEFAULT NULL, `add_ time` int (11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1

An error occurred while trying to export data using into outfile in navicat:

Select * from users into outfile 'users.txt'; / / users.txt file in mysql's dat

As shown below:

Check the permission set by mysql on the Internet, you can use it.

What is the current value of the show variables like'% secure%'; view secure-file-priv?

You can see that the secure-file-priv parameter is used to restrict LOAD DATA, SELECT... OUTFILE, and LOAD_FILE () to which specified directory. When the value of secure_file_priv is null, mysqld is not allowed to import | Export when the value of secure_file_priv is / tmp/, it means to restrict the import of mysqld | Export can only occur in the / tmp/ directory. When the value of secure_file_priv has no specific value, it means that no restrictions are imposed on the import | export of mysqld.

The exported data must be the specified path of this value before it can be exported. By default, NULL (what I'm looking at here is null) means that export is prohibited, so you need to set it.

Since I am using the my.ini file under the phpstudy,mysql installation path, set the path:

Note: path name with double quotation marks

The error remains the same after rebooting.

This parameter is not set in mysql.cnf, which means that this parameter defaults to null.

So try to modify [mysqld] in mysql.cnf and add secure_file_priv =

The final result is as follows:

Restart mysql again

You can see that users.txt is generated in the sibling test database directory.

Then execute:

Truncate users

Create an index:

Create index name using BTREE on users (name)

The methods of indexing are: BTREE, RTREE, HASH, FULLTEXT, SPATIAL. I won't repeat their differences here. There are a lot of introductions about this piece on the Internet.

Import the file into the corresponding table users.

Load data infile 'users.txt' into table users

After reading this article on how to quickly create an index for MySQL millions of data, many readers will want to know more about it. For more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.