To save time and because it’s not that smart to repeatedly retype the same things multiple times, you can user custom variables in MySQL like that: SET @store := 1, @name_id := 71, @short_desc_id := 72, @desc_id := 73; SELECT CPE.sku AS sku, CPEVN.value AS name, CPEVSD.value AS short_description, CPEVD.value AS description FROM catalog_product_entity AS […]
From time to time I stumbled over the case, that I had to drop all tables from a MySQL database. In phpMyAdmin or other GUI-based tools – in general – you only have the possibility to drop the whole database. But then you have to recreate it from the scratch.  Or you have to click-drop each table […]
This little tutorial shows how to dump wildcard selected tables from a MySQL database. That’s useful if you don’t want to dump the whole database. Background Recently I had to export a few selected tables from a very huge database. For that I could have run a query like that to export only the required […]
You can use the following query to get all duplicated records of a MySQL table having equal values for the respective field: SELECT fieldname, COUNT(*) FROM tablename GROUP BY fieldname HAVING COUNT(*) > 1 This query will give you a list of all the entries having the same value for fieldname. It also will show […]
To crate a UTF-8 database: CREATE DATABASE sns_typo3 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
With the use of HAVING you can use a result columns of a database query in itself: SELECT category_field = 3 AS ‘result’ FROM catalog_category_entity WHERE condition_field = 985 HAVING result = 1; Here the alias result (see AS ‘result’) for the only column is used as a condition with HAVING. That is by now […]
To gain external access to MySQL database (f.e. in PHPSTorm or an external PHPMyAdmin) you can do: GRANT necessary privileges: CREATE USER ‘wdn_live_user’@’%’ IDENTIFIED BY ‘***’; GRANT USAGE ON *.* TO ‘wdn_live_user’@’%’ IDENTIFIED BY ‘***’ WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; GRANT ALL PRIVILEGES ON `wdn_live`.* TO ‘wdn_live_user’@’%’; If the user already […]
$> mysql -u USEERNAME -p $> mysql> USE dbname3; # enter Database changed $> mysql> source ./relative_path_to_file.sql; # enter
To create a UTF-8 Database: CREATE DATABASE my_very_nice_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci; For external access, have a look at MySQL – external Access.
