Recent

Author Topic: SQLite3 - comparing integers  (Read 1336 times)

Birger52

  • Sr. Member
  • ****
  • Posts: 304
Re: SQLite3 - comparing integers
« Reply #15 on: November 30, 2020, 06:30:38 pm »
It is quite different.
All my numbers as it is, will be saved as TEXT, even tho the attribute(column) is defined as integer.
So indirectly I'm using both extra space and CPU for some affinity adjustments that are not really necessary.

I have worked quite a lot with MySQL - and i believe it does require quotes around external data (or maybe I just made a habit of doing so? ;) ).
Now I have to do quite a lot of changes, as it is not really optimal to use them in SQLite3...

But glad to get the insight
 ;)
Lazarus 2.0.8 FPC 3.0.4
Win7 64bit
Playing and learning - strictly for my own pleasure.

rvk

  • Hero Member
  • *****
  • Posts: 4465
Re: SQLite3 - comparing integers
« Reply #16 on: November 30, 2020, 06:42:26 pm »
With PRAGMA table_info(tablename); you can get the type info of all fields.

And yes, MySQL also seems to have some conversion between TEXT and NUMBER.
But for example PostgreSQL and Firebird and Microsoft SQL etc, have not.
https://stackoverflow.com/questions/6781976/mysql-quote-numbers-or-not

Quote
MySQL is a lot like PHP, and will auto-convert data types as best it can. Since you're working with an int field (left-hand side), it'll try to transparently convert the right-hand-side of the argument into an int as well, so '9' just becomes 9.

But both MySQL and SQLite (and all other databases) do NOT require quotes around numbers.
That's why I'm in the habit of only really using quotes for TEXT  ;)
(Especially because other databases don't do auto-convert)

 

TinyPortal © 2005-2018