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
757 def calc_found_rows
758   clone(:calc_found_rows => true)
759 end
complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
691 def complex_expression_sql_append(sql, op, args)
692   case op
693   when :IN, :"NOT IN"
694     ds = args[1]
695     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
696       super(sql, op, [args[0], ds.from_self])
697     else
698       super
699     end
700   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
701     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
702       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
703       func = ~func if op == :'!~'
704       return literal_append(sql, func)
705     end
706 
707     sql << '('
708     literal_append(sql, args[0])
709     sql << ' '
710     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
711     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
712     sql << ' '
713     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
714     literal_append(sql, args[1])
715     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
716       sql << " ESCAPE "
717       literal_append(sql, "\\")
718     end
719     sql << ')'
720   when :'||'
721     if args.length > 1
722       sql << "CONCAT"
723       array_sql_append(sql, args)
724     else
725       literal_append(sql, args[0])
726     end
727   when :'B~'
728     sql << "CAST(~"
729     literal_append(sql, args[0])
730     sql << " AS SIGNED INTEGER)"
731   else
732     super
733   end
734 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
740 def constant_sql_append(sql, constant)
741   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
742     sql << 'CURRENT_TIMESTAMP(6)'
743   else
744     super
745   end
746 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
769 def delete_from(*tables)
770   clone(:delete_from=>tables)
771 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
749 def distinct(*args)
750   args.empty? ? super : group(*args)
751 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
775 def explain(opts=OPTS)
776   # Load the PrettyTable class, needed for explain output
777   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
778 
779   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
780   rows = ds.all
781   Sequel::PrettyTable.string(rows, ds.columns)
782 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
785 def for_share
786   lock_style(:share)
787 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
795 def full_text_sql(cols, terms, opts = OPTS)
796   terms = terms.join(' ') if terms.is_a?(Array)
797   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
798 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
808 def insert_ignore
809   clone(:insert_ignore=>true)
810 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
814 def insert_select(*values)
815   return unless supports_insert_select?
816   # Handle case where query does not return a row
817   server?(:default).with_sql_first(insert_select_sql(*values)) || false
818 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
822 def insert_select_sql(*values)
823   ds = opts[:returning] ? self : returning
824   ds.insert_sql(*values)
825 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
856 def on_duplicate_key_update(*args)
857   clone(:on_duplicate_key_update => args)
858 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
861 def quoted_identifier_append(sql, c)
862   sql << '`' << c.to_s.gsub('`', '``') << '`'
863 end
supports_cte?(type=:select) click to toggle source

MariaDB 10.2+ and MySQL 8+ support CTEs

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

MySQL does not support derived column lists

    # File lib/sequel/adapters/shared/mysql.rb
878 def supports_derived_column_lists?
879   false
880 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
884 def supports_distinct_on?
885   true
886 end
supports_group_rollup?() click to toggle source

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

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

MariaDB 10.3+ supports INTERSECT or EXCEPT

    # File lib/sequel/adapters/shared/mysql.rb
894 def supports_intersect_except?
895   db.mariadb? && db.server_version >= 100300
896 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
899 def supports_limits_in_correlated_subqueries?
900   false
901 end
supports_modifying_joins?() click to toggle source

MySQL supports modifying joined datasets

    # File lib/sequel/adapters/shared/mysql.rb
904 def supports_modifying_joins?
905   true
906 end
supports_nowait?() click to toggle source

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

    # File lib/sequel/adapters/shared/mysql.rb
909 def supports_nowait?
910   db.server_version >= (db.mariadb? ? 100300 : 80000)
911 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
915 def supports_ordered_distinct_on?
916   false
917 end
supports_regexp?() click to toggle source

MySQL supports pattern matching via regular expressions

    # File lib/sequel/adapters/shared/mysql.rb
920 def supports_regexp?
921   true
922 end
supports_returning?(type) click to toggle source

MariaDB 10.5.0 supports INSERT RETURNING.

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

MySQL 8+ and MariaDB 10.6+ support SKIP LOCKED.

    # File lib/sequel/adapters/shared/mysql.rb
930 def supports_skip_locked?
931   db.server_version >= (db.mariadb? ? 100600 : 80000)
932 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
936 def supports_timestamp_usecs?
937   db.supports_timestamp_usecs?
938 end
supports_window_clause?() click to toggle source

MySQL 8+ supports WINDOW clause.

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

MariaDB 10.2+ and MySQL 8+ support window functions

    # File lib/sequel/adapters/shared/mysql.rb
946 def supports_window_functions?
947   db.server_version >= (db.mariadb? ? 100200 : 80000)
948 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
956 def update_ignore
957   clone(:update_ignore=>true)
958 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
963 def check_not_limited!(type)
964   super if type == :truncate || @opts[:offset]
965 end
default_time_format() click to toggle source

The strftime format to use when literalizing time (Sequel::SQLTime) values.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
968 def default_time_format
969   db.supports_timestamp_usecs? ? super : "'%H:%M:%S'"
970 end
default_timestamp_format() click to toggle source

The strftime format to use when literalizing timestamp (Time/DateTime) values.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
973 def default_timestamp_format
974   db.supports_timestamp_usecs? ? super : "'%Y-%m-%d %H:%M:%S'"
975 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
979 def delete_from_sql(sql)
980   if joined_dataset?
981     sql << ' '
982     tables = @opts[:delete_from] || @opts[:from][0..0]
983     source_list_append(sql, tables)
984     sql << ' FROM '
985     source_list_append(sql, @opts[:from])
986     select_join_sql(sql)
987   else
988     super
989   end
990 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
 993 def insert_columns_sql(sql)
 994   values = opts[:values]
 995   if values.is_a?(Array) && values.empty?
 996     sql << " ()"
 997   else
 998     super
 999   end
1000 end
insert_ignore_sql(sql) click to toggle source

MySQL supports INSERT IGNORE INTO

     # File lib/sequel/adapters/shared/mysql.rb
1003 def insert_ignore_sql(sql)
1004   sql << " IGNORE" if opts[:insert_ignore]
1005 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
1013 def insert_on_duplicate_key_update_sql(sql)
1014   if update_cols = opts[:on_duplicate_key_update]
1015     update_vals = nil
1016 
1017     if update_cols.empty?
1018       update_cols = columns
1019     elsif update_cols.last.is_a?(Hash)
1020       update_vals = update_cols.last
1021       update_cols = update_cols[0..-2]
1022     end
1023 
1024     sql << " ON DUPLICATE KEY UPDATE "
1025     c = false
1026     co = ', '
1027     values = '=VALUES('
1028     endp = ')'
1029     update_cols.each do |col|
1030       sql << co if c
1031       quote_identifier_append(sql, col)
1032       sql << values
1033       quote_identifier_append(sql, col)
1034       sql << endp
1035       c ||= true
1036     end
1037     if update_vals
1038       eq = '='
1039       update_vals.map do |col,v| 
1040         sql << co if c
1041         quote_identifier_append(sql, col)
1042         sql << eq
1043         literal_append(sql, v)
1044         c ||= true
1045       end
1046     end
1047   end
1048 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
1051 def insert_values_sql(sql)
1052   values = opts[:values]
1053   if values.is_a?(Array) && values.empty?
1054     sql << " VALUES ()"
1055   else
1056     super
1057   end
1058 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
1061 def join_type_sql(join_type)
1062   if join_type == :straight
1063     'STRAIGHT_JOIN'
1064   else
1065     super
1066   end
1067 end
limit_sql(sql) click to toggle source

MySQL allows a LIMIT in DELETE and UPDATE statements.

     # File lib/sequel/adapters/shared/mysql.rb
1070 def limit_sql(sql)
1071   if l = @opts[:limit]
1072     sql << " LIMIT "
1073     literal_append(sql, l)
1074   end
1075 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
1080 def literal_blob_append(sql, v)
1081   if v.empty?
1082     sql << "''"
1083   else
1084     sql << "0x" << v.unpack("H*").first
1085   end
1086 end
literal_false() click to toggle source

Use 0 for false on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1089 def literal_false
1090   '0'
1091 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
1094 def literal_float(v)
1095   if v.infinite? || v.nan?
1096     raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL"
1097   else
1098     super
1099   end
1100 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
1103 def literal_string_append(sql, v)
1104   sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'"
1105 end
literal_true() click to toggle source

Use 1 for true on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1108 def literal_true
1109   '1'
1110 end
multi_insert_sql_strategy() click to toggle source

MySQL supports multiple rows in VALUES in INSERT.

     # File lib/sequel/adapters/shared/mysql.rb
1113 def multi_insert_sql_strategy
1114   :values
1115 end
non_sql_option?(key) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1117 def non_sql_option?(key)
1118   super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update
1119 end
requires_emulating_nulls_first?() click to toggle source

MySQL does not natively support NULLS FIRST/LAST.

     # File lib/sequel/adapters/shared/mysql.rb
1122 def requires_emulating_nulls_first?
1123   true
1124 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
1156 def select_calc_found_rows_sql(sql)
1157   sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows]
1158 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
1134 def select_lock_sql(sql)
1135   lock = @opts[:lock]
1136   if lock == :share
1137     if !db.mariadb? && db.server_version >= 80000
1138       sql << ' FOR SHARE'
1139     else
1140       sql << ' LOCK IN SHARE MODE'
1141     end
1142   else
1143     super
1144   end
1145 
1146   if lock
1147     if @opts[:skip_locked]
1148       sql << " SKIP LOCKED"
1149     elsif @opts[:nowait]
1150       sql << " NOWAIT"
1151     end
1152   end
1153 end
select_only_offset_sql(sql) click to toggle source
     # File lib/sequel/adapters/shared/mysql.rb
1126 def select_only_offset_sql(sql)
1127   sql << " LIMIT "
1128   literal_append(sql, @opts[:offset])
1129   sql << ",18446744073709551615"
1130 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
1161 def select_with_sql_base
1162   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1163 end
update_ignore_sql(sql) click to toggle source

MySQL supports UPDATE IGNORE

     # File lib/sequel/adapters/shared/mysql.rb
1008 def update_ignore_sql(sql)
1009   sql << " IGNORE" if opts[:update_ignore]
1010 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
1166 def uses_with_rollup?
1167   true
1168 end