Category: One liners

https://dev.mysql.com/doc/en/load-data.html

BIT values cannot be loaded using binary notation (for example, b’011010′).

$ cat test.txt

b'101010'
0b111000

-----

CREATE TABLE loadTest (b BIT(6));

LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE loadTest;

/*--------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1406 | Data too long for column 'b' at row 1 |
| Warning | 1406 | Data too long for column 'b' at row 2 |
+---------+------+--------------------------------------*/

-- Note the wrong values:

SELECT BIN(b) FROM loadTest;
/*-------+
| BIN(b) |
+--------+
| 111111 |
| 111111 |
+-------*/

TRUNCATE loadTest;

LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE loadTest (@b) 
  SET b = CAST(CONV(TRIM('''' FROM SUBSTRING_INDEX(@b, 'b', -1)), 2, 10) AS UNSIGNED);

SELECT BIN(b) FROM loadTest;
/*-------+
| BIN(b) |
+--------+
| 101010 |
| 111000 |
+-------*/

Deconstruction:

SUBSTRING_INDEX(@b, 'b', -1)

removes the leading ‘b’ or ‘0b’.

TRIM('''' FROM ...)

removes the quotes if there are any.

CONV(..., 2, 10)

converts the string of 0s and 1s into a string of decimal digits.

CAST(... AS UNSIGNED)

turns the string of decimal digits into an integer.
MySQL automatically casts integers into bits when inserting into a BIT type.

Somebody asked on Freenode. I don’t know why they wanted it. How would you drop all MySQL users who do not have “GRANT ALL ON *.* … WITH GRANT OPTION”? That is, drop any users who have ‘N’ in any of the privilege columns in `mysql`.`user`.

My solution shown below. Did you think of a different approach?

I used SQL to build SQL which built more SQL. Get the list of ‘priv’ columns from information_schema.columns. Build a query that looks for ‘N’ in any of those columns (it feels funny to search for a constant in a list of fields instead of a field in a list of constants, but it’s perfectly legal). Use the results to build the DROP USER statement.

mysql -BNe "SELECT CONCAT('SELECT CONCAT(''DROP USER '', QUOTE(user), ''@'', QUOTE(Host), '';'') FROM mysql.user WHERE ''N'' IN (', GROUP_CONCAT(column_name), ')') FROM information_schema.columns WHERE table_schema = 'mysql' AND table_name = 'user' AND column_name LIKE '%priv';" | mysql -BN | mysql