Sie sind aktuell nicht angemeldet.

Funktionen: Einloggen  |  Neu registrieren  |  Suchen

Re: MySql
Autor: JOMA
Datum: 30.06.03 10:33
Hallo Wütender! Lies Dir das mal durch: Posted by Corey Jackson on Thursday March 13 2003, @10:05pm [Delete] [Edit] Looking for a BOOLEAN datatype? As a professional Programmer/Analyst by trade, I have much experience with application development and databases. Mainly Microsoft® SQL Server™ and MySQL™. In SQL Server™, a boolean data type is called a BIT, meaning 0 or 1, true or false. I think the MySQL™ developers need to add a BOOLEAN data type that stores either a 0 or 1. But until then I think the best method is to use an ENUM('0','1') NOT NULL. Although the MySQL™ documentation states that a TINYINT(1) is a synonym for BIT and BOOL data types, it is not the most logical method. Simply because 0 equals false and a value between 1 and 127 equals true (or 1 to 255 equals true in the unsigned version). Although this method is efficient in only requiring 1 byte of storage, it is an illogical solution because of the possibility of other values >1 being allowed. Another alternative is the CHAR(0) datatype. The ideology here is that a value of NULL equals false and a value of "" (empty string, non-null value) equals true. Again, an excellent form of efficiency in only storing 1 byte of data. The drawback however is that some scripting engines and languages cannot differentiate between a NULL and a "" (empty string), thus making your different data, essentially, the same or equal to each other. I have done extensive research on this and have deciphered that the best method to mimic a boolean datatype in MySQL™ is to use an ENUM('0','1') NOT NULL. If an ENUM is not declared as NOT NULL, then a NULL value will be allowed and the default value is NULL. I would also suggest defaulting it to a value of '0' or '1', depending on the most common value for the situation. Yes, the ENUM stores a string so 0 is really '0' and 1 is really '1'. As far as storage is concerned I'm not 100% positive whether it is 1 or 2 bytes, although rest assured it is a maximum of 2 bytes. This discrepancy exists because of lack of clarity in the MySQL™ documentation. It states that the storage required for a column of type ENUM, is 1 or 2 bytes, depending on the number of enumeration values. But since 2 enumeration values is minute compared to it's maximum of 65535 values, perhaps it is only storing 1 byte of data. Especially since the data being stored is really only 1 byte in length, a zero or one. Ok, so your convinced about the ENUM. But you may ask, why not use ENUM('y','n') or ENUM('true','false')? The storage size will be identical for the ENUM('y','n') but for backward and forward-compatibility most enterprise databases use 0 or 1 for booleans. So in the event that you would want to switch or replicate to another database provided by a different vendor, your data would most likely need not be updated before exporting and importing. Additionally, most programming languages use 0's and 1's as values when working programmatically, especially with checkboxes in a visual environment, thus requiring less cast conversions before executing a Transact SQL command. However, if the front-end your working with uses 'true' or 'false' values explicitly, 'yes' or 'no' values, or 'on' or 'off' values, then set your ENUM to the respective common values. But keep in mind that if you use ENUM('true','false') then you will most likely be storing 2 bytes as opposed to 1 byte for each record. A true boolean stores 1 byte of data consisting of either integers: 0 or 1. Ideally, a boolean should store 1 bit. (8 bits = 1 byte). But some booleans have the potential for a null or uninitialized value. Thus, I conclude, that until MySQL™ develops a true boolean datatype, using an ENUM('0','1') NOT NULL, is the best solution to date. References: http://www.mysql.com/doc/en/Column_types.html http://www.mysql.com/doc/en/ENUM.html http://www.mysql.com/doc/en/Storage_requirements.html http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp 0

MySql	120	sst_edwin	29.06.03 08:05
Re: MySql	63	sst_edwin	30.06.03 09:57
Re: MySql	472	JOMA	30.06.03 10:33
Re: MySql	54	sst_edwin	30.06.03 11:04

Sie sind nicht angemeldet! Um auf diesen Beitrag zu antworten oder neue Beiträge schreiben zu können, müssen Sie sich zunächst anmelden. Einloggen  |  Neu registrieren

Funktionen:  Zum Thema  |  Gesamtübersicht

Suchen