module Sequel::MySQL::DatasetMethods

Dataset methods shared by datasets that use MySQL databases.

Constants

MATCH_AGAINST
MATCH_AGAINST_BOOLEAN

Public Instance Methods

calc_found_rows() click to toggle source

Sets up the select methods to use SQL_CALC_FOUND_ROWS option.

dataset.calc_found_rows.limit(10)
# SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 10
    # File lib/sequel/adapters/shared/mysql.rb
761 def calc_found_rows
762   clone(:calc_found_rows => true)
763 end
complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
695 def complex_expression_sql_append(sql, op, args)
696   case op
697   when :IN, :"NOT IN"
698     ds = args[1]
699     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
700       super(sql, op, [args[0], ds.from_self])
701     else
702       super
703     end
704   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
705     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
706       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
707       func = ~func if op == :'!~'
708       return literal_append(sql, func)
709     end
710 
711     sql << '('
712     literal_append(sql, args[0])
713     sql << ' '
714     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
715     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
716     sql << ' '
717     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
718     literal_append(sql, args[1])
719     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
720       sql << " ESCAPE "
721       literal_append(sql, "\\")
722     end
723     sql << ')'
724   when :'||'
725     if args.length > 1
726       sql << "CONCAT"
727       array_sql_append(sql, args)
728     else
729       literal_append(sql, args[0])
730     end
731   when :'B~'
732     sql << "CAST(~"
733     literal_append(sql, args[0])
734     sql << " AS SIGNED INTEGER)"
735   else
736     super
737   end
738 end
constant_sql_append(sql, constant) click to toggle source

MySQL’s CURRENT_TIMESTAMP does not use fractional seconds, even if the database itself supports fractional seconds. If MySQL 5.6.4+ is being used, use a value that will return fractional seconds.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
744 def constant_sql_append(sql, constant)
745   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
746     sql << 'CURRENT_TIMESTAMP(6)'
747   else
748     super
749   end
750 end
delete_from(*tables) click to toggle source

Sets up the select methods to delete from if deleting from a joined dataset:

DB[:a].join(:b, a_id: :id).delete
# DELETE a FROM a INNER JOIN b ON (b.a_id = a.id)

DB[:a].join(:b, a_id: :id).delete_from(:a, :b).delete
# DELETE a, b FROM a INNER JOIN b ON (b.a_id = a.id)
    # File lib/sequel/adapters/shared/mysql.rb
773 def delete_from(*tables)
774   clone(:delete_from=>tables)
775 end
distinct(*args) click to toggle source

Use GROUP BY instead of DISTINCT ON if arguments are provided.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
753 def distinct(*args)
754   args.empty? ? super : group(*args)
755 end
explain(opts=OPTS) click to toggle source

Return the results of an EXPLAIN query as a string. Options:

:extended

Use EXPLAIN EXTENDED instead of EXPLAIN if true.

    # File lib/sequel/adapters/shared/mysql.rb
779 def explain(opts=OPTS)
780   # Load the PrettyTable class, needed for explain output
781   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
782 
783   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
784   rows = ds.all
785   Sequel::PrettyTable.string(rows, ds.columns)
786 end
for_share() click to toggle source

Return a cloned dataset which will use LOCK IN SHARE MODE to lock returned rows.

    # File lib/sequel/adapters/shared/mysql.rb
789 def for_share
790   lock_style(:share)
791 end
full_text_sql(cols, terms, opts = OPTS) click to toggle source

MySQL specific full text search syntax.

    # File lib/sequel/adapters/shared/mysql.rb
799 def full_text_sql(cols, terms, opts = OPTS)
800   terms = terms.join(' ') if terms.is_a?(Array)
801   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
802 end
insert_ignore() click to toggle source

Sets up the insert methods to use INSERT IGNORE. Useful if you have a unique key and want to just skip inserting rows that violate the unique key restriction.

dataset.insert_ignore.multi_insert(
  [{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT IGNORE INTO tablename (name, value) VALUES (a, 1), (b, 2)
    # File lib/sequel/adapters/shared/mysql.rb
812 def insert_ignore
813   clone(:insert_ignore=>true)
814 end
insert_select(*values) click to toggle source

Support insert select for associations, so that the model code can use returning instead of a separate query.

    # File lib/sequel/adapters/shared/mysql.rb
818 def insert_select(*values)
819   return unless supports_insert_select?
820   # Handle case where query does not return a row
821   server?(:default).with_sql_first(insert_select_sql(*values)) || false
822 end
insert_select_sql(*values) click to toggle source

The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.

    # File lib/sequel/adapters/shared/mysql.rb
826 def insert_select_sql(*values)
827   ds = opts[:returning] ? self : returning
828   ds.insert_sql(*values)
829 end
on_duplicate_key_update(*args) click to toggle source

Sets up the insert methods to use ON DUPLICATE KEY UPDATE If you pass no arguments, ALL fields will be updated with the new values. If you pass the fields you want then ONLY those field will be updated. If you pass a hash you can customize the values (for example, to increment a numeric field).

Useful if you have a unique key and want to update inserting rows that violate the unique key restriction.

dataset.on_duplicate_key_update.multi_insert(
  [{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2)
# ON DUPLICATE KEY UPDATE name=VALUES(name), value=VALUES(value)

dataset.on_duplicate_key_update(:value).multi_insert(
  [{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2)
# ON DUPLICATE KEY UPDATE value=VALUES(value)

dataset.on_duplicate_key_update(
  value: Sequel.lit('value + VALUES(value)')
).multi_insert(
  [{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2)
# ON DUPLICATE KEY UPDATE value=value + VALUES(value)
    # File lib/sequel/adapters/shared/mysql.rb
860 def on_duplicate_key_update(*args)
861   clone(:on_duplicate_key_update => args)
862 end
quoted_identifier_append(sql, c) click to toggle source

MySQL uses the nonstandard ‘ (backtick) for quoting identifiers.

    # File lib/sequel/adapters/shared/mysql.rb
865 def quoted_identifier_append(sql, c)
866   sql << '`' << c.to_s.gsub('`', '``') << '`'
867 end
supports_cte?(type=:select) click to toggle source

MariaDB 10.2+ and MySQL 8+ support CTEs

    # File lib/sequel/adapters/shared/mysql.rb
870 def supports_cte?(type=:select)
871   if db.mariadb?
872     type == :select && db.server_version >= 100200
873   else
874     case type
875     when :select, :update, :delete
876       db.server_version >= 80000
877     end
878   end
879 end
supports_derived_column_lists?() click to toggle source

MySQL does not support derived column lists

    # File lib/sequel/adapters/shared/mysql.rb
882 def supports_derived_column_lists?
883   false
884 end
supports_distinct_on?() click to toggle source

MySQL can emulate DISTINCT ON with its non-standard GROUP BY implementation, though the rows returned cannot be made deterministic through ordering.

    # File lib/sequel/adapters/shared/mysql.rb
888 def supports_distinct_on?
889   true
890 end
supports_group_rollup?() click to toggle source

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

    # File lib/sequel/adapters/shared/mysql.rb
893 def supports_group_rollup?
894   true
895 end
supports_intersect_except?() click to toggle source

MariaDB 10.3+ supports INTERSECT or EXCEPT

    # File lib/sequel/adapters/shared/mysql.rb
898 def supports_intersect_except?
899   db.mariadb? && db.server_version >= 100300
900 end
supports_limits_in_correlated_subqueries?() click to toggle source

MySQL does not support limits in correlated subqueries (or any subqueries that use IN).

    # File lib/sequel/adapters/shared/mysql.rb
903 def supports_limits_in_correlated_subqueries?
904   false
905 end
supports_modifying_joins?() click to toggle source

MySQL supports modifying joined datasets

    # File lib/sequel/adapters/shared/mysql.rb
908 def supports_modifying_joins?
909   true
910 end
supports_nowait?() click to toggle source

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

    # File lib/sequel/adapters/shared/mysql.rb
913 def supports_nowait?
914   db.server_version >= (db.mariadb? ? 100300 : 80000)
915 end
supports_ordered_distinct_on?() click to toggle source

MySQL’s DISTINCT ON emulation using GROUP BY does not respect the query’s ORDER BY clause.

    # File lib/sequel/adapters/shared/mysql.rb
919 def supports_ordered_distinct_on?
920   false
921 end
supports_regexp?() click to toggle source

MySQL supports pattern matching via regular expressions

    # File lib/sequel/adapters/shared/mysql.rb
924 def supports_regexp?
925   true
926 end
supports_returning?(type) click to toggle source

MariaDB 10.5.0 supports INSERT RETURNING.

    # File lib/sequel/adapters/shared/mysql.rb
929 def supports_returning?(type)
930   (type == :insert && db.mariadb? && db.adapter_scheme != :jdbc) ? (db.server_version >= 100500) : false
931 end
supports_skip_locked?() click to toggle source

MySQL 8+ and MariaDB 10.6+ support SKIP LOCKED.

    # File lib/sequel/adapters/shared/mysql.rb
934 def supports_skip_locked?
935   db.server_version >= (db.mariadb? ? 100600 : 80000)
936 end
supports_timestamp_usecs?() click to toggle source

Check the database setting for whether fractional timestamps are suppported.

    # File lib/sequel/adapters/shared/mysql.rb
940 def supports_timestamp_usecs?
941   db.supports_timestamp_usecs?
942 end
supports_window_clause?() click to toggle source

MySQL 8+ supports WINDOW clause.

    # File lib/sequel/adapters/shared/mysql.rb
945 def supports_window_clause?
946   !db.mariadb? && db.server_version >= 80000
947 end
supports_window_functions?() click to toggle source

MariaDB 10.2+ and MySQL 8+ support window functions

    # File lib/sequel/adapters/shared/mysql.rb
950 def supports_window_functions?
951   db.server_version >= (db.mariadb? ? 100200 : 80000)
952 end
update_ignore() click to toggle source

Sets up the update methods to use UPDATE IGNORE. Useful if you have a unique key and want to just skip updating rows that violate the unique key restriction.

dataset.update_ignore.update(name: 'a', value: 1)
# UPDATE IGNORE tablename SET name = 'a', value = 1
    # File lib/sequel/adapters/shared/mysql.rb
960 def update_ignore
961   clone(:update_ignore=>true)
962 end