Showing entries 11 to 20 of 82
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: MySQL Developer (reset)
Setting SQL_MODE

In MySQL, the @@sql_mode parameter should generally use ONLY_FULL_GROUP_BY. If it doesn’t include it and you don’t have the ability to change the database parameters, you can use a MySQL PSM (Persistent Stored Module), like:

Create the set_full_group_by procedure:

-- Drop procedure conditionally on whether it exists already.
DROP PROCEDURE IF EXISTS set_full_group_by;

-- Reset delimter to allow semicolons to terminate statements.
DELIMITER $$

-- Create a procedure to verify and set connection parameter.
CREATE PROCEDURE set_full_group_by()
  LANGUAGE SQL
  NOT DETERMINISTIC
  SQL SECURITY DEFINER
  COMMENT 'Set connection parameter when not set.'
BEGIN

  /* Check whether full group by is set in the connection and
     if unset, set it in the scope of the connection. */
  IF NOT EXISTS
    (SELECT NULL
     WHERE  REGEXP_LIKE(@@SQL_MODE,'ONLY_FULL_GROUP_BY'))
  THEN
    SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
  END IF; …
[Read more]
Dynamic Drop Table

I always get interesting feedback on some posts. On my test case for discovering the STR_TO_DATE function’s behavior, the comment was tragically valid. I failed to cleanup after my test case. That was correct, and I should have dropped param table and the two procedures.

While appending the drop statements is the easiest, I thought it was an opportunity to have a bit of fun and write another procedure that will cleanup test case tables within the test_month_name procedure. Here’s sample dynamic drop_table procedure that you can use in other MySQL stored procedures:

CREATE PROCEDURE drop_table
( table_name  VARCHAR(64))
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE stmt VARCHAR(1024);
 
  /* Set a session variable with two parameter markers. */
  SET @SQL := CONCAT('DROP TABLE ',table_name);
 
  /* Check if the …
[Read more]
str_to_date Function

As many know, I’ve adopted Learning SQL by Alan Beaulieu as a core reference for my database class. Chapter 7 in the book focuses on data generation, manipulation, and conversion.

The last exercise question in my check of whether they read the chapter and played with some of the discussed functions is:

  1. Use one or more temporal function to write a query that convert the ’29-FEB-2024′ string value into a default MySQL date format. The result should display:
    +--------------------+
    | mysql_default_date |
    +--------------------+
    | 2024-02-29         |
    +--------------------+
    1 row in set, 1 warning (0.00 sec)
    

If you’re not familiar with the behavior of MySQL functions, this could look like a difficult problem to solve. If you’re risk inclined you would probably try the STR_TO_DATE function but if you’re not risk inclined the description of the %m specifier might suggest you …

[Read more]
Case Sensitive Comparison

Sometimes you hear from some new developers that MySQL only makes case insensitive string comparisons. One of my students showed me their test case that they felt proved it:

SELECT STRCMP('a','A') WHERE 'a' = 'A';

Naturally, it returns 0, which means:

  • The values compared by the STRCMP() function makes a case insensitive comparison, and
  • The WHERE clause also compares strings case insensitively.

As a teacher, you’re gratified that the student took the time to build their own use cases. However, in this case I had to explain that while he was right about the STRCMP() function and the case insensitive comparison the student used in the WHERE clause was a choice, it wasn’t the only option. The student was wrong to conclude that MySQL couldn’t make case sensitive string comparisons.

I modified his sample by adding the required BINARY keyword for a case sensitive comparison in …

[Read more]
Read CSV with Python

In 2009, I showed an example of how to use the MySQL LOAD DATA INFILE command. Last year, I updated the details to reset the secure_file-priv privilege to use the LOAD DATA INFILE command, but you can avoid that approach with a simple Python 3 program like the one in this example. You also can use MySQL Shell’s new parallel table import feature, introduced in 8.0.17, as noted in a comment on this blog post.

The example requires creating an avenger table, avenger.csv file, a readWriteData.py Python script, run the readWriteData.py Python script, and a query that validates the insertion of the avenger.csv file’s data into the avenger table. The complete code in five steps using the sakila demonstration database:

  • Creating the …
[Read more]
MySQL Query Performance

Working through our chapter on MySQL views, I wrote the query two ways to introduce the idea of SQL tuning. That’s one of the final topics before introducing JSON types.

I gave the students this query based on the Sakila sample database after explaining how to use the EXPLAIN syntax. The query only uses only inner joins, which are generally faster and more efficient than subqueries as a rule of thumb than correlated subqueries.

SELECT   ctry.country AS country_name
,        SUM(p.amount) AS tot_payments
FROM     city c INNER JOIN address a
ON       c.city_id = a.city_id INNER JOIN customer cus
ON       a.address_id = cus.address_id INNER JOIN payment p
ON       cus.customer_id = p.customer_id INNER JOIN country ctry
ON       c.country_id = ctry.country_id
GROUP BY ctry.country;

It generated the following tabular explain plan output:

[Read more]
MySQL DropIndexIfExists

In reply to a question about how to conditionally drop an index on a table in MySQL. It appears the syntax doesn’t exist. However, maybe it does and I missed it. If I did miss it, I’m sure somebody will let me know. However, I simply have a dropIndexIfExists stored procedure for this type of database maintenance.

Below is my dropIndexIfExists stored procedure:

-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropIndexIfExists;

-- Change the default semicolon delimiter to write a PSM
-- (Persistent Stored Module) or stored procedure.
DELIMITER $$

-- Create the procedure.
CREATE PROCEDURE dropIndexIfExists
( pv_table_name  VARCHAR(64)
, pv_index_name  VARCHAR(64))
BEGIN

  /* Declare a local variable for the SQL statement. */
  DECLARE stmt VARCHAR(1024);

  /* Set a session variable with two parameter markers. */
  SET @SQL := CONCAT('ALTER TABLE ',pv_table_name,'DROP INDEX ',pv_index_name);

  /* Check if the constraint exists. …
[Read more]
MySQL WITH Clause

When I went over my example of using the WITH clause to solve how to use a series of literal values in data sets, some students got it right away and some didn’t. The original post showed how to solve a problem where one value in the data set is returned in the SELECT-list and two values are used as the minimum and maximum values with a BETWEEN operator. It used three approaches with literal values:

  • A list of Python dictionaries that require you to filter the return set from the database through a range loop and if statement that mimics a SQL BETWEEN operator.
  • A WITH clause that accepts the literals as bind variables to filter the query results inside the query.
  • A table design that holds the literals values that an analyst might use for reporting.

It was the last example that required elaboration. I explained you might build a web form that uses a table, and the table could allow a …

[Read more]
MySQL with CTEs

As an example for my class on the usefulness of Common Table Expressions (CTEs), I created three examples with Python. They extend an exercise in Chapter 9 on subqueries from Learning SQL by Alan Beaulieu. All of the examples work with the sakila sample database.

These bullets describe the examples:

  1. Uses local variables and a range for loop and if statement that uses the variables to evaluate and add an element to the derived table (or query result set) from MySQL.
  2. Uses a CTE with substitution variables from the Python program, which eliminates the need to evaluate and add an element to the query result set because the query does that.
  3. Uses a table to hold the variables necessary to evaluate and add the element to the query result set.

This is the first Python program:

# Import the library.
import sys
import …
[Read more]
MySQL SQL Filters

An interesting outcome of teaching SQL is discovering what skills new users require. One that I continuously rediscover is how to build a test case for various elements of SQL. This is a small article on querying with filters in the WHERE clause.

There are several of the exercises in Alan Beaulieu’s Learning SQL, 3rd Edition that would benefit from example setup. For example, Chapter 4 provides a snapshot of the payment table but doesn’t provide any instructions.

You can create an exercise_4_2 table with the following SQL statement if you plan to change the data:

CREATE TABLE exercise_4_2 AS
SELECT payment_id
,      customer_id
,      amount
,      payment_date
FROM   payment
WHERE  payment_id BETWEEN 101 AND 120;

Alternatively, you can create an exercise_4_2 view with the following SQL statement if you plan to only query the data:

CREATE VIEW exercise_4_2 AS
SELECT payment_id
, …
[Read more]
Showing entries 11 to 20 of 82
« 10 Newer Entries | 10 Older Entries »