Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>In <strong>MySQL</strong>, the number of <code>OR</code> conditions that can be included in a SQL statement is limited by the maximum length of a SQL statement, which is limited by the <code>max_allowed_packet</code> variable setting.</p> <p>SHOW VARIABLE LIKE 'max_allowed_packet` </p> <p>(Note: the value for <code>max_allowed_packet</code> is specified in bytes not characters, something to be aware of if your SQL statement includes multi-byte characters).</p> <p>When faced with creating a statement with a large list of <code>OR</code> conditions, there is usually a different way to perform the same operation, such as an <code>IN (list)</code> or a <code>JOIN</code> operation.</p> <p>A more efficient approach in MySQL is going to be create a table and load it from a file. (The "big rock" difficulty with this approach is going to be getting the characterset encodings to be compatible.)</p> <p>If you can produce a .CSV file from SQL Server (with one line per ID to be deleted) in either a latin1 or utf8 encoding, then characterset matching is more of a speed bump than a boulder. </p> <p>On the MySQL side:</p> <pre><code>CREATE TABLE ids_to_delete (id INT PRIMARY KEY); LOAD DATA INFILE '/tmp/ids_to_delete.csv' INTO TABLE ids_to_delete FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; -- rows to be deleted (save a backup of this if you don't already have a mysqldump) SELECT t.id FROM my_target_table t JOIN ids_to_delete d ON d.id = t.id ; -- perform the delete -- DELETE t.* -- FROM my_target_table t -- JOIN ids_to_delete d -- ON d.id = t.id ; </code></pre> <hr> <p>For loading just character representation of unsigned integer values (<code>^[0-9]*$</code>), there shouldn't be any problem with translation. The encoding for digit characters '0' thru '9' are x'30' thru x'39', and the windows style CR+LF is x'0d0a', for both latin1 and utf8.</p>
    singulars
    1. This table or related slice is empty.
    1. PONumber of OR conditions in SQL/MySQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USspencer7593
    1. USspencer7593
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI see. Well are queries UNICODE or ANSI encoding? I am asking because I want to calculate maximum number of conditions (in characters) that I can set.
      singulars
      1. PO
      1. USMiloš Ljumović
    2. CO`SHOW GLOBAL VARIABLES LIKE 'character%'` and `SHOW VARIABLES LIKE 'character%' will show the database characterset and your client characterset. (latin1 is a single byte characterset, utf8 uses one byte for character for most common characters, but two or more bytes for the more esoteric characters.)
      singulars
      1. PO
      1. USspencer7593
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload