MongoDB implements mysql primary key autoincrement

By default, a _ id field is used as the autoincrement auto-increment implementation. The _ id field type is objectid (objectid is a 12-byte BSON type ). ObjectId.

Now, due to database migration, some of the central mysql locations tables in the project are migrated to mongodb. For locations, using the mongodb 2d loc index can perform faster data retrieval, while avoiding complicated query by longitude and latitude by the original mysql.

The main requirements for data migration are as follows:

1. The migration of database tables does not affect the relationship between tables, and the primary and foreign key relationships between tables are maintained.

2. The lat and lng fields are merged into a field loc in the new mongodb table, and the loc is also indexed in 2d.

The main impact on the second point is in the code. Let's take a look at it here. We will not describe it in detail.

Now, the main thing to do is to maintain the relationship between the table and the primary and foreign keys. Take event and event_locations as an example.

The event contains a foreign key location_id, which is the primary key id of event_locations. Now, the event_locations table is created in mongodb, and the relationship between event_locations and the primary foreign key reference of the event must be maintained.

Because the mongodb table has a primary key such as _ id by default, we now have two options for data migration and mongodb event_locations table structure construction.

1. The event_locations primary key uses _ id to replace the id of the original mysql event_locations with _ id. That is, the following table structure

View source code printing help
1 {"_ id": ObjectId ("51243bdb383ca83413000000"), "topic_id": 103874, "name": "xixi aaahah", "address": "xxxbbbb cccccccccc ", "loc": [30.11, 112.02], "created": 1361329114, "updated": 0}
Advantage and disadvantage analysis

The advantage is that the default _ id primary key value is used for fast and convenient retrieval. _ Id auto-increment does not need to be maintained, and mongodb does it by itself.

The disadvantage is that the location_id foreign key in the event is obvious. The original type is int. Now we use the _ id objectid type as the primary key id, and the location_id type must be changed to char (32. The program code must be modified.

The biggest drawback is that the current project has been launched. Directly changing the database table may cause unpredictable problems, which may put a lot of pressure on operation and maintenance. Currently, the location_id data of the event must be synchronously updated.

2. Maintain the structure of the event_locations table and use the id to maintain the primary-foreign key relationship with event location_id.

The code is as follows:	Copy code
{"_ Id": ObjectId ("51243bdb383ca83413000000"), "id": 2354, "topic_id": 103874, "name": "xixi aaahah", "address ": "xxxbbbb cccccc", "loc": [30.11, 112.02], "created": 1361329114, "updated": 0}

Advantage and disadvantage analysis

The advantage is that you do not need to change the data type of event location_id, and the data of location_id does not need to be modified. You only need to import event_locations data to the new table.

The disadvantage is that you need to maintain the auto-increment attribute of id, while mongodb has the auto-increment attribute only by default. Therefore, we need to implement the auto-increment attribute of id in some way. _ Id occupies the storage space (the default _ id can be deleted, but it will be saved for future use ).

I currently use 2nd methods, mainly for the following reasons:

1. The project has been launched and user data already exists. Directly changing data tables and operating data poses a great risk. It is strongly not recommended.

2. event_locations data almost does not need to be synchronized. You only need to import the data to a new mongodb table in one way.

3. The minimum change of program code. Currently, the change of program code is the implementation of the new loc (lng + lat) storage and id auto-increment. In addition to this, replace all IDs with _ IDs, and change the data type from int to string.

After determining the solution, the main problem is how to implement id auto-increment?

There are also two methods

1. Traditionally, before event_locations insert, the full table id desc is sorted to obtain the largest id value. The id value + 1 is used to obtain the latest id value, thus realizing auto-increment.

2. Use mongodb's findAndModify () method. An independent ids table is used to record all values that may require id auto-increment.

It also briefly describes the advantages and disadvantages of the two methods to implement id auto-increment.

Method 1

Advantages: You do not need to build ids tables (tool tables) to avoid maintenance.

Disadvantage: each time you sort the full table index and find the maximum id record, + 1 is performed.

Second

Advantages: findandmodify () atomic operation (for more information about atomic operations, see here), search for and modify, and directly return the content of this document for the current operation.

Disadvantage: you need to construct and maintain the ids table, that is, a record exists in all tables that may need auto-increment in the project.

No matter which method you use, you must modify the program code, that is, the id autoincrement part needs to be implemented by the program. I am using the second method.

The main consideration is the performance problem. Avoid sorting all indexes on event_locations before each insert operation. The maintenance of tables such as ids is simple and performance is negligible. The detailed table structure is as follows:

The code is as follows:	Copy code
{"_ Id": ObjectId ("5147ca1a08c2c1bc12abdb4c"), "id": 2356, "tablename": "topic "} // _ Id: Default primary key, mongodb auto-incrementing // Id & nbsp; the auto-increment primary key value in the table // Tablename table name

Obtain the core code of the next id, which inherits the ids Model of mongodb. One of the methods is dedicated to doing this.

The code is as follows:

Copy code

/** * Building ids is used to store the auto-incrementing id values of all tables. Combined with findandmodify (), ids of the corresponding tables are returned. * @ Param string $ tablename table name * @ Return int */ Public function get_next_id ($ tablename = 'topic '){ // Db. ids. findAndModify ({update: {$ inc: {id: 1 }}, query: {tablename: 'topic '}, new: true }); $ Data = array ( 'Findandmodify' => 'kids ', 'Update' => array ('$ Inc' => array ('id' => 1 )), 'Query' => array ('tablename' => $ tablename ), 'New' => true ); $ Idrecord = $ this-> db ()-> command ($ data ); $ Newid = $ idrecord ['value']; Return $ newid ['id']; } // Equivalent to db. ids. findAndModify ({query: {tablename: 'topic '}, update: {$ inc: {id: 1 }}});

After obtaining the latest id value, insert event_locations must add the id to the SQL statement (the original id is autoincrement, and the id field is not in the SQL statement ). And then the same business process.

Extension:

Sometimes you may encounter some strange requirements. For example, a table contains two auto-incrementing IDs to implement... In this way, the above table structure cannot be implemented.

The code is as follows:	Copy code
1 <pre> // _ id default primary key, mongodb auto-increment 2 // field names in the fieldname table 3 // The id value in the fieldval table 4 // tablename table name </pre>

That's all.

Sometimes you may encounter some strange requirements, such as auto-increment of id starting from the specified value, such as 200.

In fact, you can use this sentence.

The code is as follows:	Copy code
Db. ids. findAndModify ({query: {tablename: 'topic '}, update: {id: 200, tablename: 'topic '}});

In code implementation, you must pass tablename in the update operation because the update operation updates the record, if you do not enter tablename, the returned document will have only one id field... Note that event_locations id does not have a primary key or any index, unless you add it. Therefore, we strongly recommend that you add an Index here.

The code is as follows:	Copy code
Db. event_locations.ensureIndex ({id: 1 });