this is the definition of the index in the MySQL table recommendation in Alibaba"s Java development manual, where: the primary key index is named pk_ field name
isn"t an index created for the primary key by default? how do you name this index?
ask Boss MySQL to answer questions
Primary key index naming should be for Oracle or other databases. MySQL has not found the syntax for specifying the primary key index name. Generally, the primary key index name defaults to PRIMARY.
MySQL and Oracle have differences in primary key indexes. Oracle's primary key index is an additional index with unique and non-empty attributes, while MySQL's table itself is a clustered index, and the index key is the primary key, so instead of building an index for the primary key, the primary key is used as the index key and the B-tree structure is used to organize the table data.
for the name of the primary key index, it is only for the database that can manually specify the name of the primary key index. During management, no one is sure what the name of the index is. The first is the uncertainty within the database, you can't predict what the index name is, and the second is related to the specification. In the absence of normative constraints, some people let the database determine the index name, and some people like to specify the name themselves. So when managing the primary key, it is inevitable to confirm which columns are the primary key and what the corresponding index is.
so for ease of management and standardization, simply unify the naming conventions for primary keys.
the name automatically created is different each time, which is not convenient for subsequent scripted management.
you can use
create table testpk(
id int,
nm text,
CONSTRAINT `pk_id` PRIMARY KEY (`id`)
);
specifies the constraint name, but ignores the name assigned to the primary key for Primary key, MySQL.
In MySQL, the name of a PRIMARY KEY is PRIMARY.
such as the title. see that MySQL generates a tree for the primary key, and the leaf node holds the row data corresponding to the primary key. The secondary index leaf node holds the value of the primary key. excuse me: does the leaf node of th...
EXPLAIN SELECT s.id, s.name, s.price, s.unit, s.qty, s.remark, s.image, s.cuft, s.volume, s.status, s.vender_id AS venderId, s.booth_id AS boothId, s.booth_no AS boothNo, s.show_booth_no AS showBoothNo, s.name_en AS nameEn, ...
The index is a data structure in which the leaf node of the b + tree holds pointers to the actual rows. what about clustered indexes? what does InnoDB s clustered index mean by storing B+Tree indexes and data rows in the same structure? Does mean that...
take MySQL as an example, what is the difference (principle and efficiency) between in and exists and between not in and not exists? What about using indexes? Say that not in and < > will not use indexes, won t you? ...
has such a logic, a piece of data, if there is in the database, then update, if it does not exist, then insert. But now there are ten thousand. So how to accomplish the task efficiently? ...
when there are too many SQL pages (for example, limit 1000000,20), the efficiency will be significantly reduced. How to optimize it? Here, take mysql as an example ...
when querying, you need to query the scope according to the time field, timestamp type. Statement does not have subqueries, associated queries, only look up this table. it takes about 4.5 seconds to find 50, 000 results. This field has been indexed, bu...
CREATE TABLE test1 ( id int (11) NOT NULL AUTO_INCREMENT, name varchar (20) DEFAULT NULL, code varchar (50) DEFAULT NULL, PRIMARY KEY ( id ), KEY idx_code ( code ) CREATE TABLE test2 ( id int (11) NOT NULL AUTO_INCREMENT, name varc...
problem description take over a new database, today found table data 50G, and then various indexes 75G, of which three large indexes are each around 23g, and most of the large indexes are bound to timestamps. what result do you expect? What is the e...
there are two tables Table 1: Student number 1 | Student number 2 | Student number 3 001 | 002 | 003 Table 2: Student number | name 001 | Zhang San 002 | Li Si 003 | Wang Wu desired results: student number 1 | name 1 | student number 2 | nam...
problem description there is a user table with in it. uid regdate reg_package reg_channel ... now I have a SQL query for user data SELECT * FROM `user` ORDER BY `regdate` DESC LIMIT 0,50; there is a multi-column index on this table. i...
< H1 > description < H1 > first of all, there is a table with a table structure of CREATE TABLE `article_like` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT id , `article_id` int(10) unsigned NOT NULL COMMENT id , `user_id` int(...
MySQL table with the following structure CREATE TABLE `t_article` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `content` varchar(255) NOT NULL COMMENT , `like_count` int(10) unsigned NOT NULL DEFAULT 0 COMMENT , PRIMARY KEY (...
for example, I always thought that select * from table where index column = xx and AMISOM column = yy must take the index of index column. but today I found that is not the case . tested (MySQL 5.6.16): when the union condition can match to th...
I have seen a lot of articles on mysql indexes that say that if a field has a small range of values (a large number of duplicate values), there is no need to establish an index. but my actual test: user_type_id field has an index: then executi...
reference article: Innodb lock mechanism: Next-Key Lock talk about-jyzhou-blog Park Why should Next-Key Lock be designed as a left open and right closed interval? Can t be designed as a gap lock? Why is right closed? ask the boss to answer. < ...
the database currently has 1 million data (it has been running for about half a month) the following sentence takes 6 seconds to run (1 million results for where alone), and both user_id and time are indexed ...
Test database downloaded on github. Index: explain select: query the scope of the date field of the employees table if you want to query the recruits in a certain period of time, 1. Why interpret the results, using the type field or ALL? What doe...