Tips and Tricks Archives | Bravehartk2's Blog

Save typing effort for complex MySQL queries with custom variables or procedures

22 October 201722 October 2017 SheldonNo Comments

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 CPE
  LEFT JOIN catalog_product_entity_varchar AS CPEVN
    ON (CPE.entity_id = CPEVN.entity_id
        AND CPEVN.attribute_id = @name_id)
  LEFT JOIN catalog_product_entity_text AS CPEVSD
    ON (CPE.entity_id = CPEVSD.entity_id)
       AND CPEVSD.attribute_id = @short_desc_id
  LEFT JOIN catalog_product_entity_text AS CPEVD
    ON (CPE.entity_id = CPEVD.entity_id
        AND CPEVD.attribute_id = @desc_id);

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 CPE

LEFT JOIN catalog_product_entity_varchar AS CPEVN

ON (CPE.entity_id = CPEVN.entity_id

AND CPEVN.attribute_id = @name_id)

LEFT JOIN catalog_product_entity_text AS CPEVSD

ON (CPE.entity_id = CPEVSD.entity_id)

AND CPEVSD.attribute_id = @short_desc_id

LEFT JOIN catalog_product_entity_text AS CPEVD

ON (CPE.entity_id = CPEVD.entity_id

AND CPEVD.attribute_id = @desc_id);

You first have to run the SET query. Afterwards the variables are available for the whole session. Then you can fire the SELECT with the custom variables. Another […]

Drop all tables from MySQL database

5 April 20173 November 2017 SheldonNo Comments

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 […]

Dump wildcard selected tables

12 September 2016 SheldonNo Comments

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 […]

Simulate a MySQL query before running it

15 April 201620 June 2017 Sheldon1 Comment

It is possible to Simulate a MySQL query before running it with the use of MySQL-Transactions and the build in ROLLBACK-Feature of MySQL: For example:

START TRANSACTION;
SELECT * FROM nicetable WHERE somthing=1;
UPDATE nicetable SET nicefield='VALUE' WHERE somthing=1;
SELECT * FROM nicetable WHERE somthing=1; #check

COMMIT;
# or if you want to reset changes 
ROLLBACK;

SELECT * FROM nicetable WHERE somthing=1; #should be the old value

START TRANSACTION;

SELECT * FROM nicetable WHERE somthing=1;

UPDATE nicetable SET nicefield='VALUE' WHERE somthing=1;

SELECT * FROM nicetable WHERE somthing=1; #check

COMMIT;

# or if you want to reset changes

ROLLBACK;

SELECT * FROM nicetable WHERE somthing=1; #should be the old value

For further information see https://dev.mysql.com/doc/refman/5.0/en/commit.html and my answer on stackoverflow.com.

Check and optimize tables in MySQL

26 November 201312 January 2017 SheldonNo Comments

To check and optimize tables of a MySQL database:

$> mysqlcheck -os <db_name> -u<username> -p

1	$> mysqlcheck -os <db_name> -u<username> -p

To achieve the same for all databases of the respective MySQL server you can run the following command:

$> mysqlcheck -Aos -u<username> -p

1	$> mysqlcheck -Aos -u<username> -p

Further information about check and optimize tables in MySQL To get a few more facts about how to check and optimize tables in MySQL, you should […]

M	T	W	T	F	S	S
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

Bravehartk2's Blog

- next level braining -

Category: Tips and Tricks

Save typing effort for complex MySQL queries with custom variables or procedures

Drop all tables from MySQL database

Dump wildcard selected tables

Simulate a MySQL query before running it

Check and optimize tables in MySQL

Share this:

Share this:

Share this:

Share this:

Share this: