• 技术文章 >数据库 >mysql教程

    MySql如何手动获取自增主键

    coldplay.xixicoldplay.xixi2020-10-13 15:07:37原创43

    mysql手动获取自增主键的方法:通过创建存储过程外加函数的方式维护一张sequences表来获取数据,代码为【last_number = last_number + increment_by】。

    mysql手动获取自增主键的方法:

    select nextval('TESTDATA') AS batchId
    CREATE 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 SQL
    BEGIN
      
      SELECT
        last_number INTO @VALUE
      FROM
        SEQUENCES
      WHERE sequence_name = seq_name;
      RETURN @VALUE;
    END;
    DROP TABLE IF EXISTS `sequences`;
    CREATE TABLE `sequences`  (
      `sequence_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_flag` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
      `order_flag` 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, 'N', 'Y', 20, 0);
    INSERT INTO `sequences` VALUES ('SCM', 'SEQ', 1, 9999999999, 1, 'N', 'N', 20, 0);
     
    SET FOREIGN_KEY_CHECKS = 1;

    更多相关免费学习推荐:mysql教程(视频)

    以上就是MySql如何手动获取自增主键的详细内容,更多请关注php中文网其它相关文章!

    本文原创发布php中文网,转载请注明出处,感谢您的尊重!
    专题推荐:mysql 自增主键
    上一篇:如何修改mysql 配置文件数据的存储路径 下一篇:mysql57服务怎么删除
    第13期线上培训班

    相关文章推荐

    • mysql如何修改字段名称• mysql怎么查看建表过程• mysql如何按月份分组查询• 如何修改mysql 配置文件数据的存储路径

    全部评论我要评论

  • 取消发布评论发送
  • 1/1

    PHP中文网