On 14 May 2019, at 5:50pm, Stephen Chrzanowski <[email protected]> wrote:
> I've seen it grumbled about before about giving a field a property of CHAR, > and have seen the correction that it should be TEXT. <https://www.sqlite.org/datatype3.html#determination_of_column_affinity> > I understand that SQLite doesn't really "care" what the contents of the field > is, but, just out of curiosity, is there a kind of performance hit on using > CHAR versus TEXT or INT versus INTEGER? [handwave: simplified for clarity] The statement used to create a table is stored in the database. Each time you open the database that statement is parsed and converted into an internal representation. It is at this point that "CHAR" is understood to mean TEXT. There is no great difference between understanding "CHAR" and understanding "TEXT'. And since it's done only when the database is opened, it happens only once, not every time you supply a value for that column. In other words, you're correct in this bit: > Other than translating from one database engine to another, and with the fact > that SQLite doesn't care what the affinity is (Other than for internal > workings), I can't seem to think that there'd be a big hit since it needs to > determine what that value is supposed to be when reporting back to the > application. This is true, but it's a bad habit to get into because CHAR and TEXT are meant to do different things. In implementations where both types exist, it is important that they not be used interchangeably. CHAR columns cannot contain Unicode text. It's based around the idea that one octet is one character and should make you think of old-fashioned things like ASCII, code pages, the ability to store control characters, etc.. On the other hand, TEXT columns are for text, and these days you have no idea what language your user will type, so that means Unicode. So the two definitions mean different things, are used for different things, and you can do stuff with TEXT you can't do with CHAR. In early implementations of CHAR SQL couldn't process CHAR without knowing a maximum number of characters, so type definitions looked like "CHAR(10)" . This meant that SQL would automatically and silently truncate the value you supplied to 10 characters. Feed in "Deuteronomy" and you get "Deuteronom" stored and the programmer could depend on that. A declaration of "CHAR" without a specified limit was understood as depending on some maximum length that that implementation of SQL could handle, e.g. 255 characters. SQLite will accept a column affinity of "CHAR(10)" or "CHAR(255)" or "CHAR" and not generate an error message, but it will not do the truncation. Using your existing table definition in SQLite produces different results. So again it's a bad idea to get used to specifying "CHAR" because someone reading your code might expect the behaviour they'd get from "CHAR". _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
