![]() ![]() More complex enums may also contains other types strings are the most common, but more complex objects are also supported. The most basic enums contain a set of zero-based ordinal values each represented by a constant, seen below in Java: We'll be addressing both of these issues in today's blog. Moreover, you'll want to prevent users from adding any values to the table that are not part of the permissible enum set. One of the complicating factors when storing enums in a database table is that their values may be numeric or alphabetic (i.e. Common examples include compass directions of NORTH, SOUTH, EAST, and WEST or the days of the week. As such, the variable may only hold one of the values that have been predefined for it. In the realm of Information Technology, or IT as it's more commonly known, an enum is a special data type that encapsulates a set of predefined constants. Storing Enums In a Database by Robert Gravelle I assumed that mysql/mariadb always uses single quotes to wrap values and doubles the quote for a literal '. Just for fun, i wrote a FSM solution which is UTF-8 safe and quote-resistant. To preempt anyone from saying something about it, no, I do not believe this method can lead to SQL injection. If you wanted to do this entire thing in SQL, you could use the following SET CONCAT('SELECT ', (SELECT SUBSTR(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE)-6) FROM information_schema.COLUMNS WHERE AND AND stmt FROM stmt ![]() So just pull out that record with a FetchRow(RunQuery(.)) and you’re done. So you just run "SELECT ".$Matches, and you have a full list of the strings in your first record! So next, we want to be able to get the real data out of that. This preg_match also stores the list of strings, escaped in weird SQL syntax, in $Matches. If the preg_match fails, NULL is returned. Preg_match('/^enum\((.*)\)$/iD', $FullEnumString, $Matches) confirms that the enum value matches what we expect, which is to say, "enum(".$STUFF.")" (with nothing before or after). Return FetchRow(RunQuery('SELECT '.$Matches)) function GetDataFromEnum($FullEnumString) RunQuery() and FetchRow() (non associative) are stand ins for your favorite DB access methods. The parameter $FullEnumString will hold the entire enum string, extracted from whatever method you want to use from all the other answers. Since everyone else is using PHP in this thread, that is what I will use. Well, the solution is to use MySQL to parse the data for you! The biggest special case character that was throwing me for a loop was single quotes, as they are encoded themselves as 2 single quotes together! So, for example, an enum with the value 'a' is encoded as enum('''a'''). The problem with every other answer in this thread is that none of them properly parse all special cases of the strings within the enum. In fact, I don't know of any elegant, purely SQL way to extract the member list of an ENUM column. You can query INFORMATION_SCHEMA and parse them out of the query result with a scripting language, but that's unnecessarily complicated. Now consider the evil ENUM: how do you extract the member list? You could query the ENUM column in your table for DISTINCT values but that will only return values that are actually used and present in the table, not necessarily all possible values. You can add or change the colors in the reference table, and your sexy order forms will automatically be updated. which can then be parsed out to dynamically generate the drop down list. If these values are stored in a reference table named 'colors', all you need is: SELECT * FROM colors. ![]() Getting a list of distinct ENUM members is a pain.Ī very common need is to populate a select-box or drop down list with possible values from the database. ![]() This is one of Chris Komlenic's 8 Reasons Why MySQL's ENUM Data Type Is Evil:Ĥ. ![]()
0 Comments
Leave a Reply. |