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
696 def calc_found_rows
697   clone(:calc_found_rows => true)
698 end
complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
630 def complex_expression_sql_append(sql, op, args)
631   case op
632   when :IN, :"NOT IN"
633     ds = args[1]
634     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
635       super(sql, op, [args[0], ds.from_self])
636     else
637       super
638     end
639   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
640     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
641       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
642       func = ~func if op == :'!~'
643       return literal_append(sql, func)
644     end
645 
646     sql << '('
647     literal_append(sql, args[0])
648     sql << ' '
649     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
650     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
651     sql << ' '
652     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
653     literal_append(sql, args[1])
654     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
655       sql << " ESCAPE "
656       literal_append(sql, "\\")
657     end
658     sql << ')'
659   when :'||'
660     if args.length > 1
661       sql << "CONCAT"
662       array_sql_append(sql, args)
663     else
664       literal_append(sql, args[0])
665     end
666   when :'B~'
667     sql << "CAST(~"
668     literal_append(sql, args[0])
669     sql << " AS SIGNED INTEGER)"
670   else
671     super
672   end
673 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
679 def constant_sql_append(sql, constant)
680   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
681     sql << 'CURRENT_TIMESTAMP(6)'
682   else
683     super
684   end
685 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
708 def delete_from(*tables)
709   clone(:delete_from=>tables)
710 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
688 def distinct(*args)
689   args.empty? ? super : group(*args)
690 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
714 def explain(opts=OPTS)
715   # Load the PrettyTable class, needed for explain output
716   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
717 
718   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
719   rows = ds.all
720   Sequel::PrettyTable.string(rows, ds.columns)
721 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
724 def for_share
725   lock_style(:share)
726 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
734 def full_text_sql(cols, terms, opts = OPTS)
735   terms = terms.join(' ') if terms.is_a?(Array)
736   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
737 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
747 def insert_ignore
748   clone(:insert_ignore=>true)
749 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
780 def on_duplicate_key_update(*args)
781   clone(:on_duplicate_key_update => args)
782 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
785 def quoted_identifier_append(sql, c)
786   sql << '`' << c.to_s.gsub('`', '``') << '`'
787 end
supports_cte?(type=:select) click to toggle source

MariaDB 10.2+ and MySQL 8+ support CTEs

    # File lib/sequel/adapters/shared/mysql.rb
790 def supports_cte?(type=:select)
791   if db.mariadb?
792     type == :select && db.server_version >= 100200
793   else
794     case type
795     when :select, :update, :delete
796       db.server_version >= 80000
797     end
798   end
799 end
supports_derived_column_lists?() click to toggle source

MySQL does not support derived column lists

    # File lib/sequel/adapters/shared/mysql.rb
802 def supports_derived_column_lists?
803   false
804 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
808 def supports_distinct_on?
809   true
810 end
supports_group_rollup?() click to toggle source

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

    # File lib/sequel/adapters/shared/mysql.rb
813 def supports_group_rollup?
814   true
815 end
supports_intersect_except?() click to toggle source

MariaDB 10.3+ supports INTERSECT or EXCEPT

    # File lib/sequel/adapters/shared/mysql.rb
818 def supports_intersect_except?
819   db.mariadb? && db.server_version >= 100300
820 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
823 def supports_limits_in_correlated_subqueries?
824   false
825 end
supports_modifying_joins?() click to toggle source

MySQL supports modifying joined datasets

    # File lib/sequel/adapters/shared/mysql.rb
828 def supports_modifying_joins?
829   true
830 end
supports_nowait?() click to toggle source

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

    # File lib/sequel/adapters/shared/mysql.rb
833 def supports_nowait?
834   db.server_version >= (db.mariadb? ? 100300 : 80000)
835 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
839 def supports_ordered_distinct_on?
840   false
841 end
supports_regexp?() click to toggle source

MySQL supports pattern matching via regular expressions

    # File lib/sequel/adapters/shared/mysql.rb
844 def supports_regexp?
845   true
846 end
supports_skip_locked?() click to toggle source

MySQL 8+ supports SKIP LOCKED.

    # File lib/sequel/adapters/shared/mysql.rb
849 def supports_skip_locked?
850   !db.mariadb? && db.server_version >= 80000
851 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
855 def supports_timestamp_usecs?
856   db.supports_timestamp_usecs?
857 end
supports_window_clause?() click to toggle source

MySQL 8+ supports WINDOW clause.

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

MariaDB 10.2+ and MySQL 8+ support window functions

    # File lib/sequel/adapters/shared/mysql.rb
865 def supports_window_functions?
866   db.server_version >= (db.mariadb? ? 100200 : 80000)
867 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
875 def update_ignore
876   clone(:update_ignore=>true)
877 end

Private Instance Methods

check_not_limited!(type) click to toggle source

Allow update and delete for limited datasets, unless there is an offset.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
882 def check_not_limited!(type)
883   super if type == :truncate || @opts[:offset]
884 end
delete_from_sql(sql) click to toggle source

Consider the first table in the joined dataset is the table to delete from, but include the others for the purposes of selecting rows.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
888 def delete_from_sql(sql)
889   if joined_dataset?
890     sql << ' '
891     tables = @opts[:delete_from] || @opts[:from][0..0]
892     source_list_append(sql, tables)
893     sql << ' FROM '
894     source_list_append(sql, @opts[:from])
895     select_join_sql(sql)
896   else
897     super
898   end
899 end
delete_limit_sql(sql)
Alias for: limit_sql
insert_columns_sql(sql) click to toggle source

MySQL doesn’t use the SQL standard DEFAULT VALUES.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
902 def insert_columns_sql(sql)
903   values = opts[:values]
904   if values.is_a?(Array) && values.empty?
905     sql << " ()"
906   else
907     super
908   end
909 end
insert_ignore_sql(sql) click to toggle source

MySQL supports INSERT IGNORE INTO

    # File lib/sequel/adapters/shared/mysql.rb
912 def insert_ignore_sql(sql)
913   sql << " IGNORE" if opts[:insert_ignore]
914 end
insert_on_duplicate_key_update_sql(sql) click to toggle source

MySQL supports INSERT … ON DUPLICATE KEY UPDATE

    # File lib/sequel/adapters/shared/mysql.rb
922 def insert_on_duplicate_key_update_sql(sql)
923   if update_cols = opts[:on_duplicate_key_update]
924     update_vals = nil
925 
926     if update_cols.empty?
927       update_cols = columns
928     elsif update_cols.last.is_a?(Hash)
929       update_vals = update_cols.last
930       update_cols = update_cols[0..-2]
931     end
932 
933     sql << " ON DUPLICATE KEY UPDATE "
934     c = false
935     co = ', '
936     values = '=VALUES('
937     endp = ')'
938     update_cols.each do |col|
939       sql << co if c
940       quote_identifier_append(sql, col)
941       sql << values
942       quote_identifier_append(sql, col)
943       sql << endp
944       c ||= true
945     end
946     if update_vals
947       eq = '='
948       update_vals.map do |col,v| 
949         sql << co if c
950         quote_identifier_append(sql, col)
951         sql << eq
952         literal_append(sql, v)
953         c ||= true
954       end
955     end
956   end
957 end
insert_values_sql(sql) click to toggle source

MySQL doesn’t use the standard DEFAULT VALUES for empty values.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
960 def insert_values_sql(sql)
961   values = opts[:values]
962   if values.is_a?(Array) && values.empty?
963     sql << " VALUES ()"
964   else
965     super
966   end
967 end
join_type_sql(join_type) click to toggle source

Transforms :straight to STRAIGHT_JOIN.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
970 def join_type_sql(join_type)
971   if join_type == :straight
972     'STRAIGHT_JOIN'
973   else
974     super
975   end
976 end
limit_sql(sql) click to toggle source

MySQL allows a LIMIT in DELETE and UPDATE statements.

    # File lib/sequel/adapters/shared/mysql.rb
979 def limit_sql(sql)
980   if l = @opts[:limit]
981     sql << " LIMIT "
982     literal_append(sql, l)
983   end
984 end
literal_blob_append(sql, v) click to toggle source

MySQL uses a preceding X for hex escaping strings

    # File lib/sequel/adapters/shared/mysql.rb
989 def literal_blob_append(sql, v)
990   if v.empty?
991     sql << "''"
992   else
993     sql << "0x" << v.unpack("H*").first
994   end
995 end
literal_false() click to toggle source

Use 0 for false on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
 998 def literal_false
 999   '0'
1000 end
literal_float(v) click to toggle source

Raise error for infinitate and NaN values

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1003 def literal_float(v)
1004   if v.infinite? || v.nan?
1005     raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL"
1006   else
1007     super
1008   end
1009 end
literal_string_append(sql, v) click to toggle source

SQL fragment for String. Doubles \ and ‘ by default.

     # File lib/sequel/adapters/shared/mysql.rb
1012 def literal_string_append(sql, v)
1013   sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'"
1014 end
literal_true() click to toggle source

Use 1 for true on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1017 def literal_true
1018   '1'
1019 end
multi_insert_sql_strategy() click to toggle source

MySQL supports multiple rows in VALUES in INSERT.

     # File lib/sequel/adapters/shared/mysql.rb
1022 def multi_insert_sql_strategy
1023   :values
1024 end
non_sql_option?(key) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1026 def non_sql_option?(key)
1027   super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update
1028 end
requires_emulating_nulls_first?() click to toggle source

MySQL does not natively support NULLS FIRST/LAST.

     # File lib/sequel/adapters/shared/mysql.rb
1031 def requires_emulating_nulls_first?
1032   true
1033 end
select_calc_found_rows_sql(sql) click to toggle source

MySQL specific SQL_CALC_FOUND_ROWS option

     # File lib/sequel/adapters/shared/mysql.rb
1065 def select_calc_found_rows_sql(sql)
1066   sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows]
1067 end
select_lock_sql(sql) click to toggle source

Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1043 def select_lock_sql(sql)
1044   lock = @opts[:lock]
1045   if lock == :share
1046     if !db.mariadb? && db.server_version >= 80000
1047       sql << ' FOR SHARE'
1048     else
1049       sql << ' LOCK IN SHARE MODE'
1050     end
1051   else
1052     super
1053   end
1054 
1055   if lock
1056     if @opts[:skip_locked]
1057       sql << " SKIP LOCKED"
1058     elsif @opts[:nowait]
1059       sql << " NOWAIT"
1060     end
1061   end
1062 end
select_only_offset_sql(sql) click to toggle source
     # File lib/sequel/adapters/shared/mysql.rb
1035 def select_only_offset_sql(sql)
1036   sql << " LIMIT "
1037   literal_append(sql, @opts[:offset])
1038   sql << ",18446744073709551615"
1039 end
select_with_sql_base() click to toggle source

Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1070 def select_with_sql_base
1071   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1072 end
update_ignore_sql(sql) click to toggle source

MySQL supports UPDATE IGNORE

    # File lib/sequel/adapters/shared/mysql.rb
917 def update_ignore_sql(sql)
918   sql << " IGNORE" if opts[:update_ignore]
919 end
update_limit_sql(sql)
Alias for: limit_sql
uses_with_rollup?() click to toggle source

MySQL uses WITH ROLLUP syntax.

     # File lib/sequel/adapters/shared/mysql.rb
1075 def uses_with_rollup?
1076   true
1077 end