It wasn't until MySQL 5.7 where the CREATE USER IF NOT EXISTS and DROP USER IF EXISTS syntax became available. Previous to that you could use SELECT IF (EXISTS... commands to do the same thing.
Here is an example of using SELECT IF (EXISTS... to drop a user.
-- Step 1: First create two test users with different ip ranges:
CREATE USER 'test'@'%' IDENTIFIED BY 'blabla';
CREATE USER 'test'@'10.%' IDENTIFIED BY 'blabla';
-- Step 2: Next set the user you want to drop:
SET @user = 'test';
SELECT (SELECT host
FROM mysql.user WHERE user = @user LIMIT 1) into @ip_range;
-- Step 3: Confirm variables have values
SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');
-- Step 4: Run the SELECT IF (EXISTS and then run the prepared statement
SELECT IF (EXISTS(
SELECT DISTINCT user
FROM mysql.user
WHERE user = @user )
,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
,concat('select \'user does not exist: ',@user,'\';')) into @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
-- Step 5: Check and see if there are additional users with the same name but different IP range, if so repeat
SELECT IF (EXISTS(
SELECT DISTINCT user
FROM mysql.user
WHERE user = @user )
,concat('Another user called \'',@user,'\'',' still exists.')
,concat('User \'',@user,'\' no longer exists.')) into @b;
SELECT @b;
Here are some actual examples I've written:
-- Clean up monitor_user user which doesn't have host value as %SET sql_log_bin = 0;
SET @user = 'monitor_user';
SELECT IFNULL((SELECThostFROM mysql.userWHEREuser = @userANDhost <> '%'LIMIT 1),'HOST_DOES_NOT_EXISTS') into @ip_range;
SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');
SELECT IF (EXISTS(
SELECTDISTINCTuserFROM mysql.userWHEREuser = @userANDhost = @ip_range)
,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
,concat('select \'user does not exist: ',@user,'@',@ip_range,'\';')) into @a;
SELECT @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATEPREPARE stmt1;
SET sql_log_bin = 1;
-- Clean up root user that has the host value which is the same as the hostnameSET sql_log_bin = 0;
SET @user = 'root';
SELECT IFNULL((SELECThostFROM mysql.userWHEREuser = @userANDhost <> '%'ANDhost = @@hostname LIMIT 1),'HOST_DOES_NOT_EXISTS') into @ip_range;
SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');
SELECT IF (EXISTS(
SELECTDISTINCTuserFROM mysql.userWHEREuser = @userANDhost = @ip_range)
,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
,concat('select \'user does not exist: ',@user,'\';')) into @a;
SELECT @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATEPREPARE stmt1;
SET sql_log_bin = 1;
-- Remove root user that has host value from previous co-masterSET sql_log_bin = 0;
SELECT (SELECThostFROM mysql.userWHEREhostNOTIN ('%','localhost','::1','127.0.0.1')
ANDhostLIKE CONCAT(LEFT(host, LENGTH(host) - 1),'%') LIMIT 1) into @old_co_master;
SELECT @old_co_master;
SET @user = 'root';
SELECT IFNULL((SELECThostFROM mysql.userWHEREuser = @userANDhost <> '%'ANDhost = @old_co_master LIMIT 1),'HOST_DOES_NOT_EXISTS') into @ip_range;
SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');
SELECT IF (EXISTS(
SELECTDISTINCTuserFROM mysql.userWHEREuser = @userANDhost = @ip_range)
,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
,concat('select \'user does not exist: ',@user,'\';')) into @a;
SELECT @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATEPREPARE stmt1;
SET sql_log_bin = 1;
I'm working on a bash script and I needed to import some data that I've compressed. However, the tables could also have foreign key constraints that I want ignored. So as I'm importing the dump files, I add "SET FOREIGN_KEY_CHECKS=0" to the file like this:
Suppose I have two commands, A and B, each of which returns a single-line string (i.e., a string with no newline character, except possibly 1 at the very end). I need a command (or sequence of piped commands) C that concatenates the output of commands A and B on the same line and inserts 1 space character between them.
Answer:
You can use tr:
{ echo "The quick"; echo "brown fox";}| tr "\n"" "
OR using sed:
{ echo "The quick"; echo "brown fox";}| sed -e 'N;s/\n/ /'
Why does exporting to a CSV locally from a remote MySQL server have to be so hard?
If you are running MySQL on RDS or don't have access to the file system on the remote server you cannot use the recommend approach to create a CSV file. This would be the recommend way...
SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
I like all the creative ways people on this post came up with to get over this:
If you know your data doesn't have commas or tabs then many of these solutions will work just fine. Such as this Example 1. Example 2 seems to account for even having commas or tabs in your data. But extra quotations in the data might be problematic.
Example 1:
mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv
Regex Explanation:
s/// means substitute what's between the first // with what's between the second //
the "g" at the end is a modifier that means "all instance, not just first"
^ (in this context) means beginning of line
$ (in this context) means end of line
So, putting it all together:
s/'/\'/ replace 'with \'
s/\t/\",\"/g replace all \t (tab)with","
s/^/\"/ at the beginning of the line place a "
s/$/\"/ at the end of the line place a "
s/\n//g replace all \n (newline) with nothing
Replace " with "" in each field --> replace(field1, '"', '""')
Surround each result in quotation marks --> concat('"', result1, '"')
Place a comma between each quoted result --> concat_ws(',', quoted1, quoted2, ...)
I liked the Example 2 which I have listed above. I've created a script that dynamically generates that type of SQL and then I execute that dynamically generated SQL.
My script runs three commands like this:
# Generate the header
mysql -uUSERNAME -pPASSWORD -hHOSTNAME -s -e "(SELECT GROUP_CONCAT(CONCAT('\"',COLUMN_NAME,'\"'),'' '' SEPARATOR ',')
FROM INFORMATION_SCHEMA.COLUMNS c WHERE TABLE_NAME = 'MY_TABLE_NAME' AND TABLE_SCHEMA = 'MY_DATABASE_NAME');" > my_report.csv
# Create SQL that will be used to get the data
mysql -uUSERNAME -pPASSWORD -hHOSTNAME -s -e "SET SESSION group_concat_max_len = 1000000;
(SELECT CONCAT('SELECT CONCAT_WS(\',\', ', GROUP_CONCAT('CONCAT(\'\"\', REPLACE(',column_name, ',\'\"\', \'\"\"\'), \'\"\')'), ') FROM MY_TABLE_NAME')
FROM information_schema.columns c
WHERE c.table_schema = MY_DATABASE_NAME
AND c.TABLE_NAME = 'MY_TABLE_NAME');" > dynamically_generated_code.sql
# Run the above SQL to get the data
mysql -uUSERNAME -pPASSWORD -hHOSTNAME -s MY_DATABASE_NAME < dynamically_generated_code.sql >> my_report.csv
Explanation:
First query puts the header values for the CSV file and writes them to the CSV file
Second query creates the SQL to pull the data so that it will be in comma separated value format.
The third query executes the SQL that was generated from step 2 and pulls the data for the CSV file and appends it to the file created in step one.
Running this command will give you some useful information and inform you on where the default options are read from and in what order:
mysqld --verbose --help | head -15
On my system this is what it returns:
# mysqld --verbose --help | head -15
2016-06-08 11:24:30 0 [Warning] Using unique option prefix log-err instead of log_error is deprecated and will be removed in a future release. Please use the full name instead.
2016-06-08 11:24:30 0 [Note] mysqld (mysqld 5.6.24-72.2-log) starting as process 25165 ...
2016-06-08 11:24:30 25165 [Note] Plugin 'FEDERATED' is disabled.
mysqld Ver 5.6.24-72.2-log for Linux on x86_64 (Source distribution)
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starts the MySQL database server.
Usage: mysqld [OPTIONS]
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-5.6
2016-06-08 11:24:30 25165 [Note] Binlog end
2016-06-08 11:24:30 25165 [Note] Shutting down plugin 'CSV'
2016-06-08 11:24:30 25165 [Note] Shutting down plugin 'MyISAM'
ENUMS are evil. When I started working as a MySQL DBA at my current company, our tables were rampant with enums. The dev managers wanted me to review all the database changes that went from dev to alpha and eventually to production for all the developers. As part of my standards, I prohibited developers to add enums to any new tables.
Male/Female, Mr/Mrs/Ms, Africa/Asia/etc: these bits of text that people use ENUM columns for are data. When you use an ENUM column, you're technically moving data from where it belongs (in actual database fields), to somewhere it doesn't (into the database metadata, specifically a column definition). This is different than putting constraints on the data, which is what we are doing when we say that a numeric column can only hold an integer, or that a date column can't be null - that's fine and quite necessary. With an ENUM we're actually storing pieces of data in a place that was only intended to hold crucial information about the model. In short, an ENUM column violates the rules of normalization. This may seem academic or pedantic, but it is actually why a lot of the other reasons on this list can be problems!
2. Changing the member list of ENUM columns is very expensive.
Invariably, what happens is this: you create an ENUM column and say "no way NEVER will this list change or need added to". But humans are really poor at estimating the entire scope of something, and even worse at predicting the future. R&D dreams up a whole new product type. Your company adds another shipping method. North America crashes into Asia.
The problem is that changing the member list for an ENUM column restructures the entire table with ALTER TABLE, which can be very expensive on resources and time. If you have ENUM('red', 'blue', 'black') but need to change it toENUM('red', 'blue', 'white'), MySQL needs to rebuild your table and look through every record to check for the now-invalid value 'black'. MySQL is literally dumb and will even do this when all you did was add a new value to the end of the member list! (It is rumored that appending an ENUM member list will be handled better in the future, but I doubt that this is a high priority feature.)
A full-table rebuild may not cause much pain on a small table, but on a large one it is possible to peg your resources for a long time. If you use a reference table instead of ENUM, changing the list is as simple as INSERT, UPDATE, or DELETE, laughably-cheap operations by comparison. It's also important to note that when altering an ENUM member list, MySQL converts any existing record values that are not included in the new ENUM definition to ' ' (an empty string). With a reference table, you have greater flexibility when renaming or eliminating list choices (more on this below).
3. It's impossible to add additional attributes or related info.
There just isn't any sane way to add related information to an ENUM column, which is a common scenario that often comes up. In our country/continent example, what happens when we need to store something like land area for each continent? We didn't envision needing that attribute, but now we do. With a reference table, we can simply extend the continent table to include a 'land_area' column and query this new data any way we would like. With an ENUM? Forget it.
Other awesome flexibilities exist due to the ability to easily extend a reference table. One common scenario is adding a column to set a flag to denote whether a choice in the reference table is discontinued. So, when your company stops selling black widgets, you can add an 'is_discontinued' column to the reference table and flag the old 'black' row. You can still query a list of currently offered colors, and maintain info about all your old orders of black widgets! Try that with an ENUM column.
4. Getting a list of distinct ENUM members is a pain.
A very common need is to populate a select-box or drop down list with possible values from the database. Like this:
Select color:
If these values are stored in a reference table named 'colors', all you need is:SELECT * FROM colors ...which can then be parsed out to dynamically generate the drop down list. You can add or change the colors in the reference table, and your sexy order forms will automatically be updated. Awesome.
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 query INFORMATION_SCHEMA and parse them out of the query result with a scripting language, but that's unnecessarily complicated. In fact, I don't know of any elegant, purely SQL way to extract the member list of an ENUM column.
5. ENUM columns may only offer limited or negligible effects on optimization.
The usual justifications for using ENUM, are centered around optimization, in the conventional sense of performance gains, and sometimes in the sense of simplifying a complicated model to be more comprehensible.
Let's look at performance. You can do a surprising number of un-optimized things with databases, but most won't affect performance until a certain scale is reached, and often our applications are never asked to scale up that far. This is important to remember because DB devs should aspire to design fully-normalized and only de-normalize when a performance problem becomes real. If you're concerned that a reference table is going to slow things down, benchmark it out both ways in your unique application on an actual dataset (or a realistic high-estimate fake dataset) and see. Just don't automatically assume a join or a reference table is going to be a bottleneck, because it probably isn't. (There is also evidence to support that ENUM isn't always appreciably faster than alternatives.)
The second optimization argument for ENUM is that it reduces the number of tables and foreign keys in your database. This is a valid argument, in the sense that it's one more little box joined to another box with some lines, and in large systems the effect of normalization can already tax the limits of human comprehension and complicate queries. This is however, why we make models, and why those models employ abstraction so we can understand them. Go ahead and draw up a new representation of your model or ER diagram that leaves out some of the little details and reference tables. Sometimes it may just seem easier to use an ENUM, but the fact that you think another reference table makes things too complicated isn't a good reason by itself.
6. You can't reuse the member-list of an ENUM column in other tables.
When you create a list of possible members in an ENUM column, there's no easy and consistent way to re-use that list in other tables. With a reference table, the same set of data can be related to as many other tables as required. Changing the list in the lone reference table, will change the available options in every other table that it is linked or joined to.
With separate ENUM columns, you would have identical duplicate member lists on two different columns in at least two different tables (that would all require consistent updating).
7. ENUM columns have noteable gotchas.
Suppose you have ENUM('blue', 'black', 'red') and you attempt to insert 'purple': MySQL actually truncates the illegal value to ' ' (an empty string). This is correct, but if we had used a reference table with a foreign key, we would have more robust data integrity enforcement.
Also, MySQL stores enum values internally as integer keys to reference ENUM members. It's easy to end up referencing the index instead of the value and vice-versa. Consider:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATETABLEtest (foobar ENUM('0', '1', '2'));
mysql> INSERTINTOtest VALUES('1'), (1);
Query OK, 2 rowsaffected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT* FROMtest;
+--------+
| foobar |
+--------+
| 1 |
| 0 |
+--------+
2 rowsinset(0.00 sec)
We inserted '1' (a string), and accidentally also inserted 1 (as a number, without quotes). MySQL correctly (but confusingly) uses our number input as an internal reference to the first item in the member list (which is actually the value '0').
8. ENUM has limited portability to other DBMS.
The ENUM data type isn't standard SQL, and beyond MySQL not many other DBMS's have native support for it. PostgreSQL, MariaDB, and Drizzle (the latter two are forks of MySQL anyway), are the only three that I know of. Should you or someone else want to move your database to another system, someone is going to have to add more steps to the migration procedure to deal with all of your clever ENUMs. If it's you, you'll undoubtedly feel less clever than you once did - and if it's someone else, they may not like you. Generally, migrating to a different database system is something that just doesn't happen that often and everybody assumes will bring out demons anyway, which is why this just squeaks in at number 8 on the list.
Criteria for when it might be okay to use enum:
1. When you're storing distinct, unchanging value sets...
A fairly good example that meets this criteria is our list of the continents. These are well-defined. Other commonly-given examples are salutations: Mr/Mrs/Ms, or playing card suits Spades/Hearts/Diamonds/Clubs. However, consider that even these examples have scenarios where you may need to extend the member list (such as when someone demands that you now need a 'Dr.' salutation, or when your card game app needs to accommodate a non-suited card like the Joker).
AND 2. You will never need to store additional related info...
Consider again Spades/Hearts/Diamonds/Clubs. There are popular card games that rely on the fact that clubs/spades are black and hearts/diamonds are red (Euchre, for example.) What happens when we need to store additional info related to suit, such as its color? If we had used a reference table, it would be a trivial matter to add this color data to the reference table in an additional column. If we use an ENUM to represent suit, it becomes much more difficult to represent the color/suit model accurately, and we're going to have to enforce it on the application level.
AND 3. The member list will contain more than 2 and less than 20 items.
If you're using an ENUM for only two values, you can always replace the ENUM with a very efficient TINYINT(1) or the even-better BIT(1) available since MySQL 5.0.3. For example: gender ENUM('male', 'female') can be changed to: is_male BIT(1). When you only have two choices, they can always be expressed as a Boolean true/false by prepending "is" to one of the member strings and renaming the column. As for less than 20: Yes, ENUM can store up to 65,535 values. No, you shouldn't try it. More than 20 becomes unwieldy and certainly more than 50 is just insane to manage and work with.
If you really still want to use ENUM:
1. Never use numbers as enum member values.
There's a reason ENUM is a string data type. Not only should you be using a numericdata type to store numbers, but ENUM has some well-documented gotchas related to the fact that MySQL references ENUM members internally using a numerical index. (See #7 above.) Just don't ever store numbers in an ENUM data type, ok?
2. Consider using strict mode.
Strict mode will at least throw an error when you try to insert an invalid value into an ENUM column. Otherwise only a warning is thrown and the value is simply set to an empty string ' ' (referenced internally as 0). Note: Errors can still be suppressed in strict mode if you use IGNORE.
Conclusion
Do what makes sense from a development/maintenance perspective, and optimize only once a performance problem becomes real - in most cases that is a strong argument for using reference tables over MySQL's ENUM datatype.
There just isn't any sane way to add related information to an ENUM column, which is a common scenario that often comes up. In our country/continent example, what happens when we need to store something like land area for each continent? We didn't envision needing that attribute, but now we do. With a reference table, we can simply extend the continent table to include a 'land_area' column and query this new data any way we would like. With an ENUM? Forget it.