1

here is the mysql

CREATE TABLE `demo` (
`account` varchar(30) NOT NULL,
`num` bigint(20) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `demo` (`account`, `num`) VALUES
('5000000000000000001', 5000000000000000001),
('5000000000000000002', 5000000000000000002);

when I write sql select records in php like this:

$accounts = ['5000000000000000001'];
$sql = 'SELECT * FROM `demo` WHERE `account` in ('. implode(',', $accounts).')';

and the $sql will cause the wrong query result. all the rows are select. then my question is

  1. what's the best column type should I choice for like number string. when strlen(account) > 20 or even length larger than 30 next time.
  2. how to write the sql when query for account collection in php.

thanks.

Mr.Lst
  • 33
  • 5
  • Your query looks like `WHERE account in (5000000000000000001)`; it should look like `WHERE account in ('5000000000000000001')` because the column is a VARCHAR. – Salman A Apr 12 '18 at 07:54
  • '...in (" ', implode('","', $accounts).' ") ' is ok, but any better solution? – Mr.Lst Apr 12 '18 at 08:04
  • [this is more elegant](https://stackoverflow.com/q/14767530/87015). As for why you are getting wrong results... your original query compares string with number which means they are both converted to floats for comparison, and for numbers that big, their float representation is same. – Salman A Apr 12 '18 at 08:49

1 Answers1

1

For storing account / IBAN numbers in database - it's common to choose decimal data type. Because it can handle long numbers - up to 65 digits in case decimal(65,0) and it is still a number, not string. So operations like MOD can be performed on this field,- good for example to validate if IBAN is valid or not.

Agnius Vasiliauskas
  • 10,935
  • 5
  • 50
  • 70