Saturday, October 25, 2014
   
Text Size
Login

MySQL Updating a database table from another table

What?
A quick article on how to populate a database column from another table using a string comparison.

Why?
I have several database tables which replicate country names and I would rather they all use the ccTLD two letter code. This article was written because it took me so long to work it out.

How?

  1.  UPDATE  `table_to_update` a 
  2.          INNER JOIN `table_to_read` b 
  3.              ON a.`CountryName` COLLATE utf8_general_ci LIKE b.`CountryName` COLLATE utf8_general_ci 
  4.  SET     a.`ccTLD` = b.`ccTLD` 

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

Applies to:
  • MySQL Database v5.0.45
  • MySQL Workbench v6.0.8.11354 build 833

What?
This is a quick article on how to get around the problem of backing up your MySQL database when attempting to "Data Export" using MySQL Workbench. This is not regarding the connection issue as I can connect to my database using MySQL Workbench (I have enabled the old authentication protocol). The error ONLY appears when I try to "data export" the database.

Why?
The quick solution for everyone else is to change/reset the password of the connecting database user, but herein lies the problem. When you read my workaround, you'll say that I haven't solved anything; but this is a production database I want to export for offline backup. I have to raise and log a formal change request and follow a workflow process in order to make a change on a live system to a service user account... Especially one used by the scripts to access the database-driven website.

How?

MySQL: Find all non-alphanumeric rows

What?
Quick note on how to do this.

I was tasked with cleaning up an english database by replacing all special alphabets (ë to e) and non-alphanumeric symbols with URL friendly characters.

How?

  1.  -- return all records that contain non-alphanumeric characters 
  2.  SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9]+$'
  3.   
  4.  -- return all records that are non-alphanumeric but ignore underscores 
  5.  SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9\_]+$'

SQL: Use CASE for Relevance column

What?
So this is a quick note to myself as I was playing with the relevance heuristics of a query. This example adds a column of relevance and sorts the rows accordingly.

How?
This has to be a real quick one for a dropdown search field which has to find relevant terms to autofill/autocomplete a search form:

  1.  -- where @ThisSearch is a posted (and sanitized) variable 
  2.   
  3.  SET @ThisSearch:="Brains"; 
  4.   
  5.  SELECT 
  6.          columnID, 
  7.          columnFullName, 
  8.          CASE 
  9.                  WHEN columnFirstName LIKE @ThisSearch THEN 20 
  10.                  WHEN columnFullName LIKE @ThisSearch THEN 10 
  11.                  WHEN columnLastName LIKE @ThisSearch THEN 10 
  12.                  WHEN columnFullName LIKE @ThisSearch THEN 1 
  13.          END as relevance 
  14.  FROM 
  15.          myTable 
  16.  WHERE 
  17.          s.columnPublished <= NOW(
  18.          AND ( 
  19.                  SOUNDEX(@ThisSearch)=columnFullSoundex 
  20.                  OR columnFullName LIKE @ThisSearch 
  21.          ) 
  22.  ORDER BY relevance DESC, columnFullName ASC 

Reorder Columns in a Table

What?
Just a quick note to myself on how to reorder columns as I was having difficulty using a phpMyAdmin interface to do this.

How?
Taken from the best forum for programming Qs&As: http://stackoverflow.com/questions/4095481/easy-way-to-re-order-columns

Method: phpMyAdmin
So in the phpMyAdmin interface, apparently, you can drag the columns by clicking and holding on the headers when viewing a table structure... Though maybe this is derived from a template and depending on if you are using MS Internet Explorer...

Can't get it working? I use whatever is most useful and Google's Chrome is the fastest browser I have. Here are some ways to do this:

Latest Posts