MYSQL - Problems using a string as primary key

Question

Possible Duplicate:
Alphanumeric Order By in Mysql

My tables are sorting my records incorrectly. The primary key is a string that as one char and its combined with a number starting from 1 ex: F1. It becomes problematic when it got to two digit numbers ex: F10. The database only check the first two characters and discard the rest. Ex:

It should look like this F1,F2,F3,...F9,F10,F11,etc.

It looks like this F1, F10, F11, F12,....,F2,F3,F4,etc.

So if you have any ideas please share it.

Note: using MySql 5 server and MySql Workbench 5.2

Here is the script code for one table in the database:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';



DROP SCHEMA IF EXISTS `RimpexDB` ;

CREATE SCHEMA IF NOT EXISTS `RimpexDB` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci ;

USE `RimpexDB` ;



-- -----------------------------------------------------

-- Table `RimpexDB`.`RpxFornecedor`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `RimpexDB`.`RpxFornecedor` ;



CREATE  TABLE IF NOT EXISTS `RimpexDB`.`RpxFornecedor` (

  `FID` VARCHAR(80) NOT NULL ,

  `FNome` VARCHAR(80) NOT NULL ,

  `FDescricao` VARCHAR(1000) NULL ,

  `FNTel` VARCHAR(45) NULL ,

  `FNCel` VARCHAR(45) NULL ,

  `FEndereco` VARCHAR(200) NOT NULL ,

  `FEmail` VARCHAR(100) NULL ,

  `FFax` VARCHAR(45) NULL ,

  `FActivo` CHAR NOT NULL ,

  `FDataAct` VARCHAR(200) NOT NULL ,

  `FDataNAct` VARCHAR(200) NULL ,

  PRIMARY KEY (`FID`) ,

  INDEX `FID` (`FID` ASC, `FNome` ASC, `FDescricao` ASC, `FNTel` ASC, `FNCel` ASC, `FEndereco` ASC, `FEmail` ASC, `FFax` ASC, `FActivo` ASC, `FDataAct` ASC, `FDataNAct` ASC) )

ENGINE = InnoDB

COMMENT = 'Fornecedor Table' ;



SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Why don't you use an integer as the primary key and `AUTO_INCREMENT`? — Mob, Sep 22 '11 at 13:35

score 2 · Answer 1 · answered Sep 22 '11 at 13:39

MySQL is not just looking at the first two characters and discarding the rest but rather sorting in lexical ordering (ie alphabetic) ordering. The type of ordering you need is sometimes referred to as natural ordering.

If you have the option of changing the PK from the string to just a number column dropping off the 'F' character for each record, you'll be much better off.

score 2 · Answer 2 · answered Sep 22 '11 at 13:41

Assuming the first character is always a letter (and only the first character is), this is what you need to do with your current data structure:

select FID
from RpxFornecedor
order by cast(substring(FID, 2) as integer)

But you should really consider changing your PK to a numeric one.

score 0 · Answer 3 · answered Sep 22 '11 at 13:39

0

One workaround could be to use F00001, F00002, ... F12345 strings as your primary key.

answered Sep 22 '11 at 13:39

Michael M.

467
3
10

MYSQL - Problems using a string as primary key

3 Answers3