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

Editor to share with you MySql manual access to self-increasing primary key method, I believe that most people do not understand, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Mysql manual access to self-increasing primary key method: through the creation of stored procedures plus functions to maintain a sequences table to obtain data, the code is [last_number = last_number + increment_by].

The method of manually obtaining the self-increasing primary key by mysql:

Maintain a sequences table to get data by creating stored procedures plus functions (one solution for lifetime use).

You can specify the size of each increase, as well as the initial number.

Select nextval ('TESTDATA') AS batchIdCREATE DEFINER= `admin` @ `% `FUNCTION `nextval` (seq_name VARCHAR (50)) RETURNS bigint (20) BEGIN UPDATE SEQUENCES SET last_number = last_number + increment_by WHERE sequence_name = seq_name; RETURN currval (seq_name); END;CREATE DEFINER= `admin` @`% `FUNCTION `currval` (seq_name VARCHAR (50)) RETURNS bigint (20) NO SQLBEGIN SELECT last_number INTO @ VALUE FROM SEQUENCES WHERE sequence_name = seq_name; RETURN @ VALUE;END;DROP TABLE IF EXISTS `sequences` CREATE TABLE `sequences` (`NULL DEFAULT owner` varchar (30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `sequence_ name` varchar (30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `min_ value` bigint (20) NULL DEFAULT 1, `max_ value` bigint (20) NULL DEFAULT NULL, `increment_ by`bigint (20) NOT NULL DEFAULT 1, `cycle_ value`varchar (1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL, `order_ value`varchar (1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL, `cache_ size` bigint (20) NOT NULL `last_ number` bigint (20) NOT NULL, PRIMARY KEY (`sequence_ name`) USING BTREE, UNIQUE INDEX `sel` (`sequence_ owner`, `sequence_ name`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact -Records of sequences-- INSERT INTO `sequences` VALUES ('SCM',' TESTDATA', 1, 9999999999, 1, 'Nice,' Yyears, 20, 0) INSERT INTO `sequences` VALUES ('SCM',' SEQ', 1, 9999999999, 1, SET FOREIGN_KEY_CHECKS = 1); SET FOREIGN_KEY_CHECKS = 1. These are all the contents of the method used by MySql to manually obtain self-increasing primary keys. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.