Database Coding – Check for NULLs

Background

Saw a code online and quickly knew it was not fully tested.

 

Preparation

Outline

  1. Create Sample Table
    • column_identity

Create Table

SQL


use lab;

CREATE TABLE if not exists `column_identity` 
(

      `id` int NOT NULL AUTO_INCREMENT
      
    , `ts` datetime NOT NULL
    
    , PRIMARY KEY (`id`)
    
) 
ENGINE=InnoDB 
;

Code

Stack Overflow

How to reset AUTO_INCREMENT in MySQL?

Source

  1. Source
    • How to reset AUTO_INCREMENT in MySQL?
      • Question
        Link
      • Answers
        • Kim Stacks

Image

Code


DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))
BEGIN

      SET @get_next_inc = CONCAT('SELECT @next_inc := max(id) + 1 FROM ',tablename,';');
      PREPARE stmt FROM @get_next_inc; 
      EXECUTE stmt; 
      SELECT @next_inc AS result;
      DEALLOCATE PREPARE stmt; 

      set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
      PREPARE stmt FROM @alter_statement;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Invoke

SQL

use lab;

START TRANSACTION;

delete from lab.column_identity where id > -1;

call reset_autoincrement ('lab.column_identity');

ROLLBACK;

 

Output
Output – Image

Output – Text

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

 

Code – Revision

Outline

  1. Please check for missing records
    • Prior to using a value see if it is null.
    • If Value is null
      • If null is a valid/expected value, keep as null
      • When null is not valid for a particular use case, set to a default value

SQL


create database IF NOT EXISTS commonDB;

use commonDB;

DELIMITER //

drop procedure if exists columnAutoincrementReset
//

CREATE PROCEDURE columnAutoincrementReset(

    IN tablename varchar(200)

)
BEGIN

      SET @sql = CONCAT(
                              'SELECT max(id) + 1 INTO @next_inc '
                            , ' FROM '
                            , tablename
                            , ';'
                       );

      PREPARE stmt FROM @sql;
  
      EXECUTE stmt;   
      
      DEALLOCATE PREPARE stmt; 
                              
      if (@next_inc is null) then
          
          set @next_inc := 1;
          
      end if;

      set @sql = concat(
                              'ALTER TABLE '
                            , tablename
                            , ' AUTO_INCREMENT = '
                            , @next_inc
                            , ';'
                       );
                        
      PREPARE stmt FROM @sql;
  
      EXECUTE stmt;
  
      DEALLOCATE PREPARE stmt;

                            
END //

DELIMITER ;

Invoke

SQL

use lab;

START TRANSACTION;


    delete from lab.column_identity where id > -1;
 
    call commonDB.columnAutoincrementReset ('lab.column_identity');

ROLLBACK;

 

Code Sharing

GitHub

Gist

Links

  1. Gist – Links
    Link

    • Kim Stacks
      • MySQL.autoIncReset.kimStacks.reset_autoincrement.sql
        Link
    • dadeniji
      • MySQL.autoIncReset.dadeniji.resetcolumnAutoincrementReset.sql
        Link

 

Summary

Quick Points:

  1. Have an eye for quick code review
  2. If you find obvious blind spots, please take to Lab
  3. Lab Result
    • If there are in fact Issues
      • Correct
      • Retest
      • Share
    • On the other hand, “No Issue
      • You would have corrected one of your blind spots

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s