MySQL ID Sequence Gaps

From ATI Chennai IT and ITES Wiki

(Difference between revisions)
Jump to: navigation, search
(NULL and Zero (0) Substitutions)
 
Line 29: Line 29:
IFNULL( NULLIF( expression, 0 ), 'a substitute for NULL or Zero')
IFNULL( NULLIF( expression, 0 ), 'a substitute for NULL or Zero')
</source>
</source>
 +
 +
== Explode Functionality ==
 +
<source lang="sql">
 +
CREATE FUNCTION SPLIT_STRING(
 +
    str VARCHAR(255)
 +
  , delim VARCHAR(12)
 +
  , pos INT
 +
) RETURNS VARCHAR(255)
 +
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
 +
      LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
 +
      delim, ''
 +
);
 +
</source>
 +
* [https://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql Reference]

Latest revision as of 13:08, 11 September 2019

SQLs for searching for missing id values in an AUTO_INCREMENT field:

-- Get Missing id values starting from 1
SELECT DISTINCT id -1
FROM tablename
WHERE id != 1 AND id - 1 NOT IN (SELECT DISTINCT id FROM tablename);
 
-- Same using JOIN
SELECT lft.id + 1 AS missing_ids
FROM tablename AS lft LEFT OUTER JOIN tablename AS rght ON lft.id + 1 = rght.id
WHERE rght.id IS NULL AND lft.id BETWEEN 1 AND (SELECT MAX(id)-1 FROM tablename);
 
-- Get first of missing id in each range
SELECT a.id+1 AS missing_ids
FROM tablename a
WHERE a.id+1 NOT IN (SELECT id FROM tablename b WHERE b.id=a.id+1)
AND a.id!=(SELECT id FROM tablename c ORDER BY id DESC LIMIT 1);

NULL and Zero (0) Substitutions

-- Substituting NULL
COALESCE( expression, 'a substitute for NULL' )
 
-- Substituting Zero
COALESCE( NULLIF( expression, 0 ), 'a substitute for Zero' )
 
-- Substituting NULL or Zero
IFNULL( NULLIF( expression, 0 ), 'a substitute for NULL or Zero')

Explode Functionality

CREATE FUNCTION SPLIT_STRING(
    str VARCHAR(255)
  , delim VARCHAR(12)
  , pos INT
) RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
       LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
       delim, ''
);
Personal tools